:::: MENU ::::
Browsing posts in: SQL Server

SQL Server mirroring with encrypted traffic

Moved databases to new servers where the endpoints for mirroring were set up with encryption, the witness was to be reused and on the old servers the endpoints were not using encryption, had to alter the endpoint on the witness to support RC4 encryption.

You can check the endpoints current configuration with:

select * from sys.database_mirroring_endpoints

The one with type_desc = DATABASE_MIRRORING is the mirroring endpoint, can only be one per instance.

If you need encryption and the endpoint has encryption_algorith_desc = NONE then it does not support encryption, can be changed by running something like this:

  (LISTENER_PORT = 5022)
FOR database_mirroring

The ROLE = WITNESS should be set to PARTNER if it’s the database principal or mirror server you are changing.



TEMPDB files in the wrong place, SQL Server wont start!

So, ok, lets say you install SQL Server, alter tempdb to place the data or log file(s) in another place and by mistake point these files to somewhere where tempdb files can’t exist, either because there is already some other tempdb file with the exact same name or the disk doesn’t exist, or whatever.

After change and restart of SQL Server it won’t start, in the log it says CREATE FILE of this tempdb file can’t be created, shutting down. See example below:

CREATE FILE encountered operating system error 32(failed to retrieve text for this error.
Reason: 15105) while attempting to open or create the physical file 'L:\SQLDBA\Log\templog.ldf'.


You need to start the instance in single user and with only master, no other databases.

So, boot the instance with (default instance):


Named instance:


Then move the tempdb file by running something like this, change this to reflect your environment ofcourse:

USE master;
MODIFY FILE (NAME = tempdev, FILENAME = 'W:\SQLDba\Data\tempdb.mdf');
MODIFY FILE (NAME = templog, FILENAME = 'L:\SQLDba\Log\templog.ldf');

Then stop and restart server normally and verify file placements with something like this:

SELECT name,
FROM   sys.master_files
WHERE  database_id = DB_ID(N'tempdb');

or I like to run, for more info:

select     sysdb.name,
 sysmaster.name as logical_file_name,
 CASE sysmaster.type_desc
 END as filetype,
 (sysmaster.size/128) as MB
 from       sys.databases sysdb,sys.master_files sysmaster
 where      sysdb.database_id = sysmaster.database_id
 and        sysdb.name not like '%snapshot%'
 order by sysdb.name, filetype

See Microsofts page on MSDN for more information, go to



Extract SQL Server service pack exe file to disk

Often I want to keep service packs on disk, extracted to files, makes patching later to next service pack easier. Can get some weird errors otherwise if the files from the previous service pack or original install files aren’t where the registry says they should be.

Extracting them is easy, to extract eg SQL Server 2008 SP3 X64, just open a command windows and run:

c:\sp\SQLServer2008SP3-KB2546951-x64-ENU /extract

This will bring up a window asking you where to place the extracted files, it will not create a new catalog so if you put C: then your C: root will be full of files, make sure to put something like c:\SP3\ or similar.



DBCC TRACEON(3499, -1)

It seems to be some kind of performance penalty running SQL Server databases in a mirrored configuration.

On the mirror server we often see a large amount of i/o waits, there have been some discussions on various blogs that it’s because of how the mirror database applies the logs it gets from the primary database, http://blog.rolpdog.com/2010/04/sql-server-database-mirroring-woes.html says it is because:

“apparently SQL Server not only ships the log data over in real-time, it also performs recovery on the DB for every transaction to minimize the failover time (which IS nice and snappy, usually <1s). Turns out, there is an undocumented trace flag that disables the per-transaction recovery process, at the cost of a higher failover delay.”

And this behaviour can be altered via trace flag 3499, I haven’t tried this yet, will do it in a couple of days, Microsoft says this is an undocumented trace flag, thus you are on your own if it does not work as intended.

SQL Server 2008 documented trace flags



Trace flag Description
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures.Scope: global or session
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected.Scope: global only
1211 Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memory. For more information, see Lock Escalation (Database Engine).

If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.

Scope: global or session

1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.Scope:global only
1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:

  • Forty percent of the memory that is used by Database Engine, exclusive of memory allocation using Address Windowing Extension (AWE). This is applicable only when the locks parameter of sp_configure is set to 0.
  • Forty percent of the lock memory that is configured by using the locks parameter of sp_configure. For more information, see Setting Server Configuration Options.

If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.

Lock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.

Scope: global or session

2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like that of parallel queries. For more information, see max degree of parallelism Option.Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Sometimes, checking may start when the server is almost idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.

Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time.

Scope: global or session

3205 By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.Scope: global or session
3226 By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.
3608 Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.
3625 Limits the amount of information returned in error messages. For more information, see Metadata Visibility Configuration.Scope: global only
4616 Makes server-level metadata visible to application roles. In SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.Scope: global only
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. The behavior of the trace flag is as follows:

  • If this is used as a startup trace flag, a memory dump is never generated. However, a memory dump may be generated if other trace flags are used.
  • If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect.

Scope: global only

7806 Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express. For more information, see Using a Dedicated Administrator Connection.Scope: global only


Example, set trace flag 3205 to on globally:

DBCC TRACEON (3205,-1)

Trace flags are set on or off by using either of the following methods:

  • Using the DBCC TRACEON and DBCC TRACEOFF commands.For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1). To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
  • Using the -Tstartup option to specify that the trace flag be set on during startup.The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option. For more information about startup options, see Using the SQL Server Service Startup Options.

Use the DBCC TRACESTATUS command to determine which trace flags are currently active.


Reconfigure Reporting Services for failover

Just a thought…

Have a Reporting Server setup, 2008R2, and the databases are in an SQL Server 2008R2 instance that is mirrored to an other instance, normal nofrills mirroring.

What I want to get working is to get the Reporting Services instance to automatically reconfigure itself to use the mirrored instance after a failover.

Read about the rsconfig utility, tried to understand how it works:

Wrote down an example

rsconfig -c -s192.168.0.20 -dreportserver -aWindows -uMILANO\administrator -psecretpassword

rsconfig {-?}
{–c connection}        scripted change with parameters, omitted if using -e
{–e unattendedaccount}    manual change with parameters given in console, omitted if using -c
{–m computername}       Required if you are configuring a remote report server instance.
{–i instancename}    SQL Server instance
{–s servername}        Specifies the SQL Server instance that hosts the report server database.
{–d databasename}    Specifies the name of the report server database.
{–a authmethod}        Windows or SQL authentication to database server
{-u username}        [domain\]username om Windows
{-p password}        Username’s password
{-t trace}        Outputs error messages to the trace log


My thought was to use the WMI alert that is being triggered on the instance to check if a database is going from principal to mirror, ( see for example http://technet.microsoft.com/sv-se/library/cc966392%28en-us%29.aspx ),   then firstly failover all databases with a script. Then on the instance where the databases are going online, to principal, let the alert trigger a script that runs the rsconfig utility with the parameters set to reconfigure it to use the databases on the mirror.

And vice versa of course if we’re failing back. Or just have

Should work right?


Have to try this out, I’ll write some more when I’ve had time to test  :-)


SQL Server manual mirror failover commands

Just a quick list of commands how to manually failover a database that is mirrored.

Assume DB name is AdventureWorks in examples below.


Manual Database Failover from the Principal instance



Changing a Mirror database to Principal (High Safety Mode)

Run on the mirror instance



Changing a Mirror database to Principal (High Performance Mode)

Changing Mirror of AdventureWorks database as primary.

Run on the mirror instance



Failover of an AlwaysOn availability group
First connect to the instance currently holding the SECONDARY, then run


Listing effective object permissions in SQL Server

Easiest and fastest is to use sp_helprotect, it’s a system stored procedure that shows you user permissions on an object, see MSDN manual for more information, some examples of usage below:

—     List all user permissions of all Database objects

—     List all user permissions of tblSalary
sp_helprotect ‘dbo.tblSalary’

—     List all user permissions of sp_Get_Salary
sp_helprotect ‘dbo.sp_Get_Salary’

—     List permissions on sp_Get_Salary for user emp_user
sp_helprotect ‘dbo.sp_Get_Salary’, ’emp_user’

—     List all user permissions of sp_Get_Salary provided by dbo
sp_helprotect ‘dbo.sp_Get_Salary’, null,’dbo’

—     List all Object type user permissions
sp_helprotect null, null,null,’o’

—     List all statement type user permissions
sp_helprotect null, null,null,’s’

Automate Backups with SQL Express

This is a blatant copy of http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressBackups&referringTitle=Home saved it here just so I’ll have an easier way of finding it later

I did not write it, just keeping a copy, see the original page for more information.



Oh, and first, a simple one-liner script to delete .bak files older than 14 days:

 forfiles -p "X:\path\to\backups\" -s -m *.bak -d 14 -c "cmd /c del @path"



How to Automate Database Backups with SQL Server Express


Jonathan Kehayias, May 1, 2008

One of the items missing from SQL Server Express is the ability to schedule jobs to run at set times since it lacks the SQL Server Agent Service. This makes scheduling automated backups of the databases in a SQL Express Instance difficult to do, though not impossible. There are a more than a few ways to go about creating backups of the databases in SQL Express and this article offers what should be a very easy to implement solution.

In order to perform the backups, the following stored procedure will need to be created in a database on your SQL Instance. Make sure that you know where this procedure is created because this is important in later steps.

Create Dependent Stored Procedure
CREATE PROCEDURE [dbo].[BackupDatabases]
 @BackupDir varchar(400),
 @DatabaseName sysname = null,
 @BackupType int = 0 -- 0=Full, 1=Differential, 2=Log

-- -- Begin Test Code
-- DECLARE @BackupDir varchar(400)
-- SET @BackupDir = 'D:\SQLBackups\Daily\'
-- -- End Test Code

-- Create worker table
 DBName varchar(500)

-- Grab the Database Names from master DB
SELECT Name FROM master.sys.databases
WHERE name = @DatabaseName
 OR @DatabaseName IS NULL

-- The below databases are not valid to backup
IF @BackupType = 0
 DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs')
ELSE IF @BackupType = 1
 DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs', 'master')
ELSE IF @BackupType = 2
 DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs', 'master', 'msdb', 'model')

IF (@BackupType < 0 OR @BackupType > 2)

-- Declare Session Variables
DECLARE @Now datetime
DECLARE @TodayStr varchar(20)
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBName varchar(300)
DECLARE @LogFileName varchar(300)
DECLARE @SQL varchar(2000)
DECLARE @Loopvar int

-- Begin looping over Databases in the Work Table
SELECT @Loopvar = min(rowID)


-- Database Names have to have [dbname] format since some names have a - or _ in the name
SET @DBName = '['+(SELECT DBName FROM @DBNames WHERE RowID = @LoopVar)+']'

-- Set the current date and time
SET @Now = getdate()

-- Create backup file date and time in DOS format yyyy_hhmmss
Set @TodayStr = convert(varchar, @Now, 112)+ '_'+replace(convert(varchar, @Now, 108), ':', '')

-- Create a variable holding the total path\filename.ext for the log backup
Set @BackupFile = @BackupDir+REPLACE(REPLACE(@DBName, '[',''), ']','')+'-'+ @TodayStr + '-FULL.BAK'

-- Provide the backup a SQL name and name in media
Set @BackupName = REPLACE(REPLACE(@DBName, '[',''), ']','')+' full backup for ' + @TodayStr

-- Generate the Dynamic SQL script variable to be executed
IF @BackupType = 0
 SET @SQL = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME = ''' +@BackupName+''', NOSKIP, NOFORMAT'
ELSE IF @BackupType = 1
 SET @SQL = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH DIFFERENTIAL, INIT, NAME = ''' +@BackupName+''', NOSKIP, NOFORMAT'
ELSE IF @BackupType = 2
 SET @SQL = 'BACKUP LOG ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME = ''' +@BackupName+''' , NOSKIP, NOFORMAT'

-- Execute the SQL Command

-- Goto the Next Database
SELECT @Loopvar = min(rowID)
WHERE RowID > @LoopVar

-- Execute Full Backup of all databases in local named instance of EXPRESS
-- to D:\SQLBackups\ using Windows Authentication
sqlcmd -S .\EXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\', @BackupType=0"

-- Execute Differential Backup of all databases in local named instance of SQLEXPRESS
-- to D:\SQLBackups\Differentials\ using SQL Login BackupUser with Password password.
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\Differentials\', @BackupType=1"

-- Execute Log Backup of all databases in local named instance of EXPRESS
-- to D:\SQLBackups\TLogs\ using Windows Authentication
sqlcmd -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\TLogs\', @BackupType=2"

-- Execute Full Backup of the WebContacts Database in local named instance of EXPRESS
-- to D:\SQLBackups\ using Windows Authentication
sqlcmd -S .\EXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\', @DatabaseName='WebContacts', @BackupType=0"


There is a sample usage section at the bottom of this stored procedure in comments, that exists as a reference for its use if there is ever a need to review how it can be called from the operating system. Once this procedure has been created, it can be called from the operating system with the use of the sqlcmd executable. The folder for this executable is generally in the Path variables for the server after SQL is installed, but in case it is not it is located in <Install Path>\90\Tools\Binn. (generally C:\Program Files\Microsoft SQL Server\90\Tools\Binn)

The next decision to be made is under what context or security account will the backups be run. Since we will be using the Task Scheduler to run the backups through a batch file we will create momentarily, you have the option to run this under a windows login account. The account will require the BackupOperator Role at a minimum. To test that you have created the user account and configured it correctly, you can run either one of the following commands from the command prompt.

To Use Windows Authentication
sqlcmd -S .\SQLEXPRESS -Q “EXEC BackupDatabases @BackupDir=’c:\’, @DatabaseName=’master’, @BackupType=0”

To Use SQL Authentication
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q “EXEC BackupDatabases @BackupDir=’c:\’, @DatabaseName=’master’, @BackupType=0”

If this is all configured correctly, you will have a file beginning with master and ending in FULL.BAK in the root c:\ folder. If this completes successfully, open Notepad up inside of Windows, and paste the corresponding command for what you want done into the file. To save it as a batch file, select File -> Save As, and change the File Type from *.txt to . then put in the filename SQLBackups.bat, and save it to the path where you want it to be maintained from.

Then open up the Windows Task Scheduler which is generally in Start -> Programs -> Accessories -> System Tools -> Scheduled Tasks. Create a new Scheduled Task, and point it to the Batch file you just save for its execution. Create the desired schedule for it to run, and then specify the user account that this schedule will run under. If you are using Windows Authentication, it should be the same account that you were logged in with in the tests above. Once you have done this, save the schedule, and you now have an automated backup process for SQL Express.

You can expand upon this by creating multiple batch files, one for Full Backups Weekly, one for Daily Differential Backups, and perhaps one for backing up the Transaction Log every four hours. Then you create schedules for each type of backup and you have now implemented Full Recovery for your SQL Express Database Instance.

For additional information please see:


Performing Common Maintenance Tasks in SQL Express


sqlcmd Utility Tutorial


How to: Create a Full Database Backup (Transact-SQL)


How to: Create a Transaction Log Backup (Transact-SQL)