SQL Server Update Statistics with FULL SCAN

Sometimes you just need a quick method to update all statistics of every table. The script below will apply a FULL SCAN , so I wouldn't recommend that you use this as regular maintenancce task . There are other command options for sp_MSforeachtable

exec sp_MSforeachtable
 @command1 = 'print ''Processing table ?''',
 @command2 = 'UPDATE STATISTICS ? WITH FULLSCAN' ,
 @command3 = 'print ''Processed table ?'''

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server BACKUP - There is insufficient system memory to run this query

On a particular server - I see these entries in the Logs, which correlate with failed BACKUP messages from the TSM server  :

BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-000002F8-0000'. Operating system error 0x8007000e(error not found).

My immediate impression was lack of  memory.

This server has 32 GB physical memory on a 32 bit Windows 2003 Enterprise Edition , so I checked the following :
-  /PAE switch in the boot.ini file (no /3GB switch)
-  maximum memory set on SQL Server (17GB)
-  AWE is set on
-  User running SQL Server - has Lock pages in memory

All this seems OK . Looking further into the Memory manager

VM Commited displays physical memory amount SQL Server allocated ising VirtualAlloc's MEM_COMMIT .
AWE allocated displays the memory amount allocated by AllocateUserPhysicalPages.
Adding these 2 will give approximately the amount SQL Server memory usage

Memory Manager
 VM Reserved = 1645036 KB
 VM Committed = 211672 KB (.201) GB
 AWE Allocated = 17408000 KB (16.6015625 GB)
 Reserved Memory = 1024 KB
 Reserved Memory In Use = 0 KB

It appears the SQL server is reaching it's memory limit .

One answer is to increase Memory allocation , another option is to investigate the cause of large memory usage 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server - sp_MSforeachtable

This is an undocumented stored procedure. It's found in the master db and accepts the following input parameters.It returns one (or greater ) rows for every table. Each table has it's own resultset .

I find it useful for managing a process such as a sql server rapid tuning for an emergency performance issue

Input parameters :

@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null

An example:

exec sp_MSforeachtable
 @command1 = 'print ''Processing table ?''',
 @command2 = 'UPDATE STATISTICS ? WITH FULLSCAN' ,
 @command3 = 'print ''Processed table ?'''

Source:Jack Vamvas (http://www.sqlserver-dba.com)

SQL SELECT INTO

A regular question is how to transfer data from one table to another efficiently.

I'm including an example of one method. But as with all approaches - ensure you have relevant SQL Server BACKUP and RESTORE  for easy rollback.

Keep in mind - the structure of the new table is defined by the SELECT statements expression attributes . One of the consequences is that you will need to transfer indices separately

 

--create new table and insert dataset

select * INTO Links_temp from Links where links_id NOT IN ('2E28BF11CFBC','B0566C4F2758')

--check count dataset  and whatever sanity checks you require

select count(*) from Links_temp

--check new resultset

select * from Links_temp

--check and compare column definition

SELECT column_name,

data_type ,

CHARacter_maximum_length

FROM information_schema.columns

WHERE table_name = 'Links_temp'

 

--rename existing Linkstable to Links240810

sp_rename '[Links]','[Links240810]'

--rename Links_temp to Links

sp_rename 'Links_temp','Links'

 

--oops!indexes didn't transfer - which they don't on a SELECT INTO

 

CREATE UNIQUE CLUSTERED INDEX [I_Links10] ON [dbo].[Links]

(

[links_id] ASC,

[name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]






CREATE NONCLUSTERED INDEX [I_Links11] ON [dbo].[Links]

(

[head_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]





GO

CREATE NONCLUSTERED INDEX [I_Links12] ON [dbo].[Links]

(

[version_series_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]





ALTER TABLE [dbo].[Links] ADD PRIMARY KEY NONCLUSTERED

(

[object_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  =

ON) ON [PRIMARY]

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Differential backup cannot be restored . BACKUP with COPY_ONLY

An Operations member of staff approached me with a problem - which I thought might be worth posting .
He was asked to do a RESTORE of a full backup and the related Differential. He executed the RESTORE for the FULL BACKUP
as :

RESTORE DATABASE MYPRODDB FROM DISK = 'E:\MYSERVER\MYDB.bak' WITH NORECOVERY;

He then attempted to RESTORE the differential , but was confronted with the following message:

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

A quick check of the SQL Server Logs indicated that there was a manual backup taken by a Developer which broke the chain . Looking at the logs below
indicates that the base backup was taken , followed by a differential . The third log entry points to a manual full backup , and the fourth entry is a differential but using 3 as the base backup . You can derive this information from reading the - full backup LSN entry on the Differential backups - and how both differentials refer to different base backups.