Shrink local database log file to save disk space

Possibly save 1 hour of your time: I notice my machine disk space is running low. So where can I save some space.

First, download a free program call WindirStat. This program will tell you in a very nice visual way of where most of your space are taken up.

So I found out from my machine there are two database log files in which both took up over 50 GB.

Solution: I backup the database first. Then I ran the following command on the specific database in SQL Server Management Studio 2012.

select * from sys.database_files --find the name of the log file

ALTER DATABASE your_database_name SET RECOVERY SIMPLE
DBCC SHRINKFILE('name_of_log', 0, TRUNCATEONLY)
ALTER DATABASE your_database_name SET RECOVERY FULL

Murach’s SQL Server 2016 for Developers

Advertisements

sql server seed identity reset

Possibly save 1 hour of your time: Sometimes after deleting some reference data in a table one want to change the identity to reseed at 1 or a certain value. The next time you insert a new record it will reseed starting at 1.

DBCC CHECKIDENT ('YourTableName', RESEED, 0);
GO

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

dacpac post deployment script not running

Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:

  1. If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
  2. After add script, right click the file and ensure Build Action is set to PostDeploy.
  3. Now build the project.
  4. Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.

Command for deploying dacpac:

“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Error: SQL71006: Only one statement is allowed per batch. A batch separator, such as ‘GO’, might be required between statements.

Possibly save 1 hour of your time: This error happens when I am adding a post deploy script as part of my database project and then do a build.

When you add this script file to your project, you need to right click the properties and set Build Action to None. This should solve the issue.

Exam Ref 70-764 Administering a SQL Database Infrastructure

Trigger and execute SQL Server Agent Job

Possibly save 2 hours of your time:

Recently, I was asked to find a generic way for non-technical administrators to be able to trigger or execute SQL Server Agent Job on their own on demand.

One solution is to build a windows authenticate administration website within our network domain. This web application will use windows authentication authenticate against specific windows user/group that has permission to trigger/execute a back end job.

public Exception ExecuteSqlAgentJob(string connectionString, string jobName)
{
Exception exception = null;

var dbConn = new SqlConnection(connectionString);
var execJob = new SqlCommand();
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = “msdb.dbo.sp_start_job”;
execJob.Parameters.AddWithValue(“@job_name”, jobName);
execJob.Connection = dbConn;

try
{
using (dbConn)
{
dbConn.Open();
using (execJob)
{
execJob.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
exception = ex;
}

return exception;
}

The application pool identity that runs this application should have access to:

  • the database which the SQL job targets
    • Go to specific database >> Security >> Users >> add login
  • msdb database
    • Go to msdb database >> Security >> Users >> add login
    • And give it SQLAgentOperatorRole membership
    • This gives permission to execute dbo.sp_start_job
    • Note: Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.