SqlServer Begin's

1.     Click Start, click Run, type  wbemtest  in the Open box, and then click  OK . 2.     In the Windows Management Instrumentation Tester dialog box, click  Connect . 3.     In the box at the top of the Connect dialog box, type  root\cimv2 , and then click  Connect . 4.     Click #Enum Instances". 5.     In the Class Info dialog box, type  Win32_OperatingSystem  in the Enter superclass name box, and then click  OK . 6.     In the Query Result dialog box, double-click the top item. Note this item starts with "Win32_OperatingSystem.Name=Microsoft..." 7.     In the Object editor dialog box, locate the  PAEEnabled  property in the Properties area and double-click on it. 8.     In the Property Editor dialog box, note the value in the Value box.

One of the clients needed its users to remotely execute a SQL job and as usual I picked this up hoping for a quick brownie point. Sure enough there was a catch and there was something to learn. Executing the job through SQLCMD was a no-brainer but getting it to execute on the remote machine was bit of challenge. On the coding Front 1    1.)     The bat file included the following code                 SQLCMD -S "[ServerName] " -E -Q "EXEC MSDB.dbo.sp_start_job @Job_Name = ' '[JobName]" 2    2.)     The Individual users were given minimum permissions  to execute the SQL job Ex. use msdb EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = ' Domain\UserLogin ' At the client machine              This took a fair bit of time till our sysadmin got me an empty VM machine....

Even though there are many ways of working around the TEMDB and user database collation conflicts when using #Temp_* tables. I ran into a situation where I couldn’t find a wayout. The problem was with the SQL Spatial function STGeometryType. Of what I realised, this function just didn’t like the #Temp_* table collation even if the table was created with same collation as the source table. I also couldn’t find a way of converting the returned value to a simple text and neither was I able to explicitly convert it to the required collation at the time of the equality operation. The query that failed look like the following SELECT   * FROM   #Temp_tempt WHERE   Geometry_SPA.STIsValid()=1 AND   Geometry_SPA.MakeValid().STGeometryType()   IN   ( 'LineString' , 'MultiLineString' )       AND   Geometry_SPA.MakeValid().STLength()<0.1 FYI – The Geomerty columns don’t have spatial collation It appeared, w...

Past weekend I encountered a situation  where there was insufficient disk space due to logfull errors. As usual I identified the log file that needed to be shrunk and attempted to shrink the file. However, to my surprise it didn’t want to shrink and threw the following error. Msg 3023, Level 16, State 2, Line 1 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. Following is the screenshot of the unused space of the database files For a moment I thought it was something wrong with the database. But later realised that there were conflict of interest to the logfile usage and whatever the operation that was using it. This could have been caused due to active transaction in the transaction log due to replication,mirroring , logshipping , some datamilipulation or a backup in progress. With...

I was recently attempting to re-establish mirroring for a client and realized the following waittype hanging on the database. I initially thought, the mirroring session was not removed correctly but after further verification it was soon obvious that the mirroring session had been discontinued and it was clean. When I attempt to kill the process PSID = 20 , I got the following message  Spid = 20 Msg 6107, Level 14, State 1, Line 1 Only user processes can be killed. At 2 am in the morning I was no mood for research , I was just interested in getting the mirroring session established and get to bed. I knew it was a matter of dropping the database and getting about with the normal business. Then I mistakenly executed the restore databases script !!!!. surprisingly the restore didn’t complain and progressed to complete without errors and I continued to setup the mirroring session, which completed successfully. Later during the day I discovered that...

I was recently investigating a server where an Enterprise evaluation edition was installed. Surprisingly they were not sure the drive for the enterprise edition and were not aware of any enterprise features used. If I were to recommend them SQL standard is a better way forward I had to be sure that no enterprise features were used. Then I recalled a licencing statement from a script of Brent Ozar. I have slightly changed his query to suit my need to find the databases with Enterprise feature. EXEC dbo . sp_MSforeachdb ' if exists( SELECT 1 FROM ?.sys.dm_db_persisted_sku_features) select ''?''' ;

Last evening I ran into the precarious situation when trying restore a database. I have cut down a lot of steps to just get to the problem.   By 9.00 pm I had done few database restores at a client and one of the large databases just wouldn’t get restored with the standard error. Msg 3702, Level 16, State 3, Line 2 Cannot drop database “DataBaseName” because it is currently in use. I went through the sysprocesses and killed all the active connections but the same message kept popping out all the time. After many attempts and 10 minutes gone I was still at square one. Tried standard opening and closing management studio ,Stopping sql agent   none would work. Finally I took the database into single user mode with Alter database set single_user with rollback immediate ( this took about 5 minutes to execute) and created hole load of informational messages in between. Then dropped the database which took another 5 minutes to take effect.

There was a query that was posted few weeks back where the performance of the application gradually deteriorated towards the end of the week but at the beginning of the week it was working optimally. What does this mean from a technical perspective? Towards the end of the week the indexes gradually get fragmented and during the weekend a optimization job reorganizes or rebuilds the index. Even though, from database maintenance perspective we saved our selves there seems to be room for improvement. If we read between lines it does say the index get fragmented fairly soon and that’s clear indication 1.)   That there is notable amount of data written to the indexes and -         2.) The index design is not optimal (or) 3    3.) The fill factor settings may need to be changed after monitoring As we were not in a state to recommend any indexing strategy to the client( due to vendor ownership) we needed to identi...