James' SQL Footprint - Blogs - SQLServerCentral

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Get Your Own Blog

If you would like to blog on SQLServerCentral.com then send an email to webmaster@sqlservercentral.com.

Contact the author

Please log in or register to contact the author of this blog

All Blogs

All Bloggers on SQL Server Central

Feeds

Subscribe to this blog

Archives for this blog

James' SQL Footprint

Love SQL Server, Love life.

Monitor Deadlock in SQL 2012

Do you still use trace flag 1204 and 1222 to monitor Deadlock? or using profile to capture deadlock? Now we are in SQL Server 2012! One of the biggest improvement of SQL 2012 is Extended Events.

Extended Events can replace SQL Profiler, and it is more powerful with less performance…

0 comments, 2,473 reads

Posted in James' SQL Footprint on 12 August 2012

Backup Master Database

The story came from a question someone asked me.

Does Master database support full recovery mode?

As I remembered, by default, Master database is in "Simple" recovery mode. I never try to put it into Full recovery mode because master database is not updated frequently, and it is very small.…

0 comments, 1,205 reads

Posted in James' SQL Footprint on 7 July 2012

New in SQL 2012 (1)

Learning new is a interesting thing. Today I read several posts regarding the SQL Server 2012 new feature:

1. Indirect Checkpoint
Prior to SQL Server 2012, SQL Server Database Engine Support 3 type of checkpoints: Automatic, manual and internal. Now there is a new checkpoint type added : Indirect Checkpoint.

Not…

0 comments, 620 reads

Posted in James' SQL Footprint on 1 July 2012

Several useful T-SQL Function

1. Using the OVER clause with aggregate functions
The following sample is from BOL:
-------------------------------------
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

0 comments, 642 reads

Posted in James' SQL Footprint on 30 June 2012

Rebuild index with "Alter Index Rebuild" or "DBCC DBREINDEX"

You can rebuild all index for a table with "Alter Index Rebuild" and "DBCC DBREINDEX" .

First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify…

1 comments, 4,094 reads

Posted in James' SQL Footprint on 23 June 2012

Shrink transaction log file

Sometimes the shrink transaction log file doesn't work.

Before we troubleshoot it, we need to know how the log file works:

Transaction Log Physical Architecture

http://msdn.microsoft.com/en-us/library/aa174526(v=sql.80).aspx

Here are some tools which can help us investigate the log file issues.

a. DBCC SQLPERF(logspace) : tell you how much transaction log is…

0 comments, 765 reads

Posted in James' SQL Footprint on 17 June 2012

Max Server Memory

Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx

you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx

Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory…

0 comments, 1,601 reads

Posted in James' SQL Footprint on 2 June 2012

Setup windows firewall for SQL Server

On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.

1 comments, 991 reads

Posted in James' SQL Footprint on 1 June 2012

Backup duration statistics

Here is a simple query which can list backup duration statistics for all database, including the max, min, avg of the backup duration. you can estimate the backup duration by this query when you plan your backup job

SELECT
  DBNAME,
  backup_type,
  MAX(sizeMB) as MaxBackupFileSizeMB,
  Max(duration) as MaxDurationSEC,
  MAX(speedMBSEC) as MaxSpeedMBperSEC,
  MIN(sizeMB) as…

0 comments, 317 reads

Posted in James' SQL Footprint on 25 May 2012

Ad hoc query optimization in SQL Server

When ad hoc queries are executed in sql server, if it is executed without parameters, and it is simple, SQL Server parameterizes the query internally to increase the possibility of matching it against an existing execution plan, that's called "Simple Parameterization"

For instance:

--clear plan cache first
DBCC FREEPROCCACHE

--run…

2 comments, 2,236 reads

Posted in James' SQL Footprint on 15 May 2012

Use Powershell script to verify backup files

Before restoring backup, we always verify the backup file first, and run the 3 command below:

1. RESTORE HEADERONLY
Returns a result set containing all the backup header information for all backup sets on a particular backup device.

2. RESTORE FILELISTONLY
Returns a result set containing a list of…

0 comments, 3,359 reads

Posted in James' SQL Footprint on 12 May 2012

Backup database in CMS

Sometime you know the database name which you want to backup, but you don't know which server it is on. Normally we will
1. run query on CMS to find out which server the database is on
2. then connect to the server and run backup query.

If you backup…

0 comments, 296 reads

Posted in James' SQL Footprint on 9 May 2012

Generate restore script automatically by backup history table

I wrote a store procedure which could generate restore script automatically by backup history table. it can help you select a best restore solution base on the backup history table.

For instance, if you backup the database as the sequence below

ID	backup_start_date	backup_type
1	2012-04-30 18:28:43.000	Full
2	2012-04-30…

2 comments, 1,252 reads

Posted in James' SQL Footprint on 30 April 2012

Setup database mirroring and log shipping together

SQL Server has multiple high availability solutions. Comparing with cluster, database mirroring and logshipping are easier to setup, and they don't need special hardware.

here is a great article about how to setup the database mirroring and log shipping together.
http://technet.microsoft.com/en-us/library/ms187016.aspx

In our system, we setup data mirroring with high-safety…

0 comments, 2,116 reads

Posted in James' SQL Footprint on 21 April 2012

script system object in resource database

When I troulbshooting the logshipping problem, I found many logshipping store procedure can not be found in the master or msdb database. for instance, in the master. [sys].[sp_add_log_shipping_secondary_primary] , it calls sys.sp_MSprocesslogshippingjob to create the copy job, however, sys.sp_MSprocesslogshippingjob is in the resource database, we can not review its code with sp_helptext command.

0 comments, 274 reads

Posted in James' SQL Footprint on 19 April 2012

Log shipping setup problem

Last week, our log shipping setup had a problem, so I spent some time to dig into the log shipping.

there are 2 monitor tables after logshipping setup
1. log_shipping_monitor_primary
This table has entries for all primary database on primary server and monitor server

2. log_shipping_monitor_secondary
This table has entries…

0 comments, 439 reads

Posted in James' SQL Footprint on 18 April 2012

Why I can not connect sql server ???

Recently, I installed sql server 2012 on windows 7 laptop, today when I wanted to login sql server with TCP/IP, I got error.

1. Enable TCP protocol
I tried to login sql server by SSMS with Server Name "MachineName\InstanceName", and select the Network protocol "TCP/IP"

but got error message below

My…

0 comments, 2,327 reads

Posted in James' SQL Footprint on 10 April 2012

Why shrink sql server data file failed?

Today I need to shrink a big data file, I know shrink file is not a best practise, and it will cause data fragment, but sometimes after you clean the database with deleting old data, you had to shrink file, it should be fine as long as you run rebuild…

2 comments, 663 reads

Posted in James' SQL Footprint on 7 April 2012

Clean SQL Server Cache

I lives alone, usually, I clean my small apartment at every weekend, wipe the table/firniture with cloth, clean the capet with vacuum cleaner, wash clothes with machine. the clea enviroment makes me feel confortable, and have a good start for the new week.

SQL Server memory cache just like…

0 comments, 2,571 reads

Posted in James' SQL Footprint on 6 April 2012

SQL Server 2012 license

Not sure if it is a good news or bad news, SQL Server 2012 license shifts from processor-based to core-based.......here are the changes

1. There is no Datacenter Edition anymore, it is merged into Enterpise Edition. Furthermore, Workgroup and Small business Editions are also being retired.

Currently there are only…

0 comments, 814 reads

Posted in James' SQL Footprint on 5 April 2012