All tables row count

Useful code to gather the rowcount of all tables, worth doing a reindex prior to this code

SELECT
SCHEMA_NAME(t1.schema_id) AS [schema],
t1.name AS [table],
i1.rows AS [row_count]
FROM sys.tables AS t1 INNER JOIN
sys.sysindexes AS i1 ON t1.object_id = i1.id AND i1.indid < 2

Space used for all databases

This is a useful script I use to list the space used for all databases on a given sql server

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused @updateusage = N'TRUE';"

The purpose of passing the argument : @updateusage , is it updates the usage information

sp_detach_db causing loss of permissions

I was using sp_detach_db to detach a database and then attempt to transfer with another DOS command. I was receiving a Access denied and couldn't figure why . Investigating closer revealed a change of permissions when using the sp_detach_db.

This was only in the situation of being a windows principal. In other words when attempting to detach a db the system resets the ACL of the file giving the windows user full control, with all permissions being removed

The workaround is to use the security context of a SQL  Logon, an example would be :

 EXECUTE AS LOGIN='sqlLogon'

 exec sp_detach_db 'my_db'

 exec xp_cmdshell 'COPY "E:\mssqlserver\mssql$inst1\data\my_db.mdf" "E:\mssqlserver\mssql$inst1\data\my_db_copy.mdf"'

 REVERT
 GO

Quick uninstall of SQL Server 2005 instance

The following command line brings up all instance names and asks to select
an instance and then a few button clicks.

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe"
/Remove

Recover from a Suspect database

--Recover from a Suspect db on SQL Server 2005 Standard Service Pack2
---Error: 3414, Severity: 21, State: 1.
--During redoing of a logged operation in database 'MY_DB', an error occurred at log record ID (5006:4238:75).
--Typically, the specific failure is previously logged as an error in the Windows Event Log service.
--Restore the database from a full backup, or repair the database.
--I progressed with these steps , to get the db back to an ONLINE state
--NOTE : This is a last resort , and I only tried , once I'd exhausted recovery from backup etc

ALTER DATABASE MY_DB SET ONLINE;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET EMERGENCY;

GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET SINGLE_USER;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET MULTI_USER;
GO
SELECT state_desc FROM sys.databases WHERE name='MY_DB';

SQL Server backup review with Powershell

I had  to produce a report to the Backup team outlining the last backup of every database on every instance.
This had to cover : full , incremental and log type backups. The idea was to compare this against their schedule , checking for disparities .

The sql server inventory in the organisation is 750 sql server instances , using SQL server 2000 and 2005. The solution had to involve iterating through a list of sql server instances , and executing some t-sql code, adding the results to an output file.Following is the steps , with relevant details

STEP 1
Create a .txt file with a list of all relevant instances , such as

MYSERVER1\INSTANCE1
MYSERVER2\INSTANCE2
MYSERVER3\INSTANC3

STEP 2
Create a .sql file with the following code , and save as backups_last_good_backups.sql. In this case I wanted to raise an ALERT , if a backup hadn't occured in 7 days , or if no backup existed at all.:

/******************************************************************
*
* SQL Server - Last known good backup
*
* Run this script against a sql server instance to check when last known
* good backups were taken
* The script displays : name of db , backup type , backup set db name , last backup
*A
******************************************************************/
SELECT  (SELECT   RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as instance,
        sd.name as name,
        bs.type,
        bs.database_name,
        max(bs.backup_start_date) as last_backup,
        note = CASE
   WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
   WHEN ISNULL(max(bs.backup_start_date),0) = 0 THEN 'ALERT'
   ELSE '---'
   END
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
        WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
        bs.type,
        bs.database_name
Order by sd.name,last_backup

 

STEP 3
Create a powershell file , and place in the following code . Note :
on line 1 is the location of the file you created in Step 1
Line 2 , invokes the SQLCMD (which needs to be installed on whichever computer you are execuring this powershell code.)
It accepts the the $svr variable , and runs the .sql code , saved in step 2. This outputs the results into "backupAudit_SQL_Server_output.txt"
in an iterative mode.

foreach ($svr in get-content "C:\instances_backup.txt"){
        invoke-expression "SQLCMD -E -S $svr -i backups_last_good_backups.sql -v inst_name=$svr -Y30 >> backupAudit_SQL_Server_output.txt"
 } 

 

STEP 4
Ensuring your powershell code and SQL code are in the same directory ,Run the powershell code in your Powershell command line - and then check the output , it should be something like :

instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER1_INST1               master                         D    master                         2009-06-27 23:59:53.000 --- 
MYSERVER1_INST1               model                          D    model                          2009-06-28 00:03:01.000 --- 
MYSERVER1_INST1               msdb                           D    msdb                           2009-06-28 00:03:07.000 --- 
MYSERVER1_INST1               Rightdbs                       I    Rightdbs                       2009-06-26 20:30:26.000 --- 
MYSERVER1_INST1               Rightdbs                       D    Rightdbs                       2009-06-28 00:03:18.000 --- 

(5 rows affected)
instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER2_INST2             master                         NULL NULL                                              NULL ALERT
MYSERVER2_INST2             model                          NULL NULL                                              NULL ALERT
MYSERVER2_INST2             msdb                           NULL NULL                                              NULL ALERT
MYSERVER2_INST2             db3nn                          D    db3nn                          2009-06-18 19:56:10.000 ALERT

 

SQL Server detected a DTC/KTM in-doubt transaction with UOW

There was an unexpected server shutdown , on a sql server box . Upon restart one of the databases went into an IN RECOVERY state ,
and once the database had reached 99% complete  the following message appeared in the SQL Server log files .

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {63E15310-78DA-42DA-8BB1-38B59FDA866E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Initially I tried the  : KILL '{63E15310-78DA-42DA-8BB1-38B59FDA866E}' WITH COMMIT   ,but this didn't resolve the issue.

The resolution was achieved by :

Type "mmc.exe" (without the quotation marks) in the Run dialog box to open Microsoft Management Console (MMC).
In MMC, on the Console menu, click Add/Remove Snap-in.
In the Add/Remove Snap-in dialog box, click Add.
In the Add Standalone Snap-in dialog box, click Component Services, and then click Add to add the snap-in.
Click Close.
In the Add/Remove Snap-in dialog box, click OK.
In the console root under Component Services, expand Component Services, point to Computers, point to My Computer, and then click Distributed Transaction Coordinator.
Select Transaction List. You see transactions in the details pane. Right-click the transaction, and then click Resolve. You now have three options: Commit, Abort, or Forget. Choose the one that is appropriate for your situation.
DTC jobs

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs'

SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

I was getting a "the user cannot access resource due to not having rights on Locked in Memory"

The solution to the problem is:

On the Start menu, click Run. In the “Open” box, type gpedit.msc.
On the “Group Policy” console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
In the right details pane, double-click Lock pages in memory.

Add the relevant account to the permissions list on this policy, the account should be the start up account for the SQL Service

Windows jobs

SQL and Markov Chains in Tennis

I’ve just completed a project at tennisMatchOdds.com which required the analysing probabilities of  outcomes in the sport  of  tennis. An algorithm created the likely outcome of the event. A further requirement of the project was to calculate likely outcomes of the event , from different points and scores during the event. For example, if player A was 1 set up what was the probability of them winning the match.

I analysed the data within SQL Server 2000 and the algorithms were created using T-SQL. There was a possibility to use Java 1.4, but the customer requested (for different commercial reasons)  for the analysis to occur in SQL. Initially , I investigated the possibility of a script library that could be imported. I could push the data through and get an immediate result. No luck. I decided to simulate the Markov Chain modelling through T-SQL.

A Markov chain model simulates changes in a finite number of states at regular time intervals. For my purposes , it was effective because I was dealing with a large set of objects which required local manipulation . For example, modify an object slightly to get a new one, also as a way to discover patterns within the different result sets. In other words, you can compute the probability of the match being in any given state.

The following code , between START CODE and END CODE, should be copied and pasted into Query Analyzer . A result set will appear that will allow you to view  , how you can programmatically start off with an initial figure and go through every possibility.

The data in “#tempNum” represents all the possibilities within my model.

-------------------------START CODE----------------------------------------

--the code is an abstraction for educational purposes .

--It is an example of simulating Markov Chain modelling with SQL Server 2000

--the key Markov Chain elements are represented with lower case SQL

--particuarly between the comments --start loop and --end loop

CREATE TABLE #tempNum (

            [id] [int] NULL ,

            [a] [int] NULL ,

            [b] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO #tempNum ([id],[a],[b])VALUES(1,4,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(2,4,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(3,4,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(4,4,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(5,4,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(6,3,4)

INSERT INTO #tempNum ([id],[a],[b])VALUES(7,3,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(8,3,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(9,3,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(10,3,0)

INSERT INTO #tempNum ([id],[a],[b])VALUES(11,2,3)

INSERT INTO #tempNum ([id],[a],[b])VALUES(12,2,2)

INSERT INTO #tempNum ([id],[a],[b])VALUES(13,2,1)

INSERT INTO #tempNum ([id],[a],[b])VALUES(14,2,0)

 

GO

 

 

 

DECLARE @playerBReturn DECIMAL(10,4)

DECLARE @playerAserver DECIMAL(10,4)

SET @playerAserver = .50

SET @playerBReturn = 1-@playerAserver

 

begin

   set nocount on

   DECLARE @initCalc DECIMAL(10,8)

   SET @initCalc = .33

  

   create table #tmp (recno int identity, a int,b int,pv DECIMAL(10,4))

   insert into #tmp (a,b,pv)

   select a,b,1 from #tempNum  order by id ASC

   declare @t int,@c int

   DECLARE @tmpPV DECIMAL(10,8),@tmpPVUpdate DECIMAL(10,8)

   DECLARE @tmpAplus INT, @tmpBplus INT  --+1

   DECLARE @tmpA INT, @tmpB INT  --+1

   set @t = (select count(*) from #tmp)

   set @c = 1

   --start loop

   while @c <= @t

   begin

      IF @c < 7

      BEGIN

                         update #tmp

                 set pv = 1.00

                 where recno=@c

      END

 

      IF @c = 7

      BEGIN

             SET @tmpPV = @initCalc

      END

      ELSE IF @c > 7

      BEGIN

             SET @tmpPV = (select pv from #tmp x where x.recno=@c-1)

      END

     

     

      IF @c = 7

      BEGIN

             update #tmp

         set pv = @initCalc

         where recno=@c

        

      END

      ELSE IF @c > 7

      BEGIN

             --deal with 0

             IF  ((SELECT b FROM #tmp x WHERE  x.recno=@c) IN (3)) SET @tmpPV =0

         SET @tmpA = (SELECT a FROM #tmp x WHERE  x.recno=@c)

         SET @tmpB = (SELECT b FROM #tmp x WHERE  x.recno=@c)

             SET @tmpAplus = ((SELECT a FROM #tmp x WHERE  x.recno=@c) + 1)

         SET @tmpBplus = ((SELECT b FROM #tmp x WHERE  x.recno=@c) + 1)

         IF @tmpAplus = 3 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpAplus = 2 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpAplus = 1 AND @tmpBplus = 4  SET @tmpBplus=3

         IF @tmpA = 2 AND @tmpB = 2  SET @tmpBplus = 2

             IF @tmpA = 2 AND @tmpB = 1  SET @tmpBplus = 1

             IF @tmpA = 2 AND @tmpB = 0  SET @tmpBplus = 0

             IF @tmpA = 1 AND @tmpB = 2  SET @tmpBplus = 2

         IF @tmpA = 1 AND @tmpB = 1  SET @tmpBplus = 1

         IF @tmpA = 1 AND @tmpB = 0  SET @tmpBplus = 0

         IF @tmpA = 0 AND @tmpB = 2  SET @tmpBplus = 2

         IF @tmpA = 0 AND @tmpB = 1  SET @tmpBplus = 1

         IF @tmpA = 0 AND @tmpB = 0  SET @tmpBplus = 0

         SET @tmpPVUpdate = (SELECT pv FROM #tmp x WHERE  a = @tmpAplus  AND b = @tmpBplus)

             update #tmp

         set pv =  ((@playerAserver * @tmpPVUpdate) +  (@playerBReturn * @tmpPV))

         where recno=@c

       

       END

      set @c = @c + 1

   end

   --end loop

   select recno,a,b,pv = pv from #tmp where a <> 4 AND b <> 4

  

   DROP TABLE #tempNum

   DROP TABLE #tmp

   

end

-------------------------END CODE---------------

sp_spaceused not refreshing

I ran the command

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

which returned all the relevant information per database on a SQL Server instance I was troubleshooting.
On the basis of the information I adjusted one of the db sizes .

I then ran the same command and notices that the information was not refreshing .

Adding "@updateusage = N'TRUE'" derived the latest information , and reflected the changes I made.

EXEC sp_msforeachdb @command1="use ? exec sp_spaceused @updateusage = N'TRUE';"

hardware jobs