July 29, 2011 at 7:26 am
Koen Verbeeck (7/29/2011)
Ugh. Another copyright violation.http://www.sqlservercentral.com/Forums/Topic1150675-391-1.aspx?Update=1
Original article:
http://www.databasejournal.com/features/mssql/sql-server-index-t-sql-statements.html
Looks like someone already removed the topic. It takes me to the forums home now.
July 29, 2011 at 7:31 am
That one goes into my briefcase :-D:hehe::w00t:
GilaMonster (7/29/2011)
Lavanyasri (7/29/2011)
I am expecting scripts .. can you provide any scripts to find the missing indexes and frequently used queries ???I'm expecting please and thank you, but I'm frequently disappointed.
There are tonnes of scripts, in the script library here and on the internet in general for missing indexes and half of my article is devoted to finding frequently running queries.
A quick search will get other scripts for you way faster than waiting for me to search for the scripts and paste them (or their links) in here.
http://www.sqlservercentral.com/Forums/Topic1149971-146-1.aspx
July 29, 2011 at 7:36 am
I'm getting way too snide and cynical. Maybe time to take a short forum break again...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2011 at 7:38 am
I'm inaugurating my "Vote for Jeff" signature. 😎
-- Gianluca Sartori
July 29, 2011 at 7:39 am
GilaMonster (7/29/2011)
I'm getting way too snide and cynical. Maybe time to take a short forum break again...
Actually made me laugh...
July 29, 2011 at 8:22 am
GilaMonster (7/29/2011)
I'm getting way too snide and cynical. Maybe time to take a short forum break again...
A little snide and cynical is part of your charm, Gail. 😉
Shake it off. Have another hot cross bun.
One a penny, two a penny...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 29, 2011 at 9:24 am
Interesting rant here: http://www.insanit.net/technology/smartphone-rants-3-what-do-i-want-a-real-windows-smartphone-when-do-i-want-it-now/
Anyone else see the need/value here? I'm struggling to see if this is something that has value.
July 29, 2011 at 9:39 am
Do you guys(als) know a way to remove the prints from the backup / restore commands?
This is what I'd like to be gone from the output :
100 percent processed.
Processed 99 pages for database 'DemoRecover', file 'DemoRecover_log' on file 1.
BACKUP LOG successfully processed 99 pages in 0.037 seconds (21.836 MB/sec).
July 29, 2011 at 9:59 am
Ninja's_RGR'us (7/29/2011)
Do you guys(als) know a way to remove the prints from the backup / restore commands?This is what I'd like to be gone from the output :
100 percent processed.
Processed 99 pages for database 'DemoRecover', file 'DemoRecover_log' on file 1.
BACKUP LOG successfully processed 99 pages in 0.037 seconds (21.836 MB/sec).
AFAIK, you cannot. These are sent along a different stream, and it's up to the client to consume them. If you return a result set, they aren't returned. Aren't these only on the messages tab?
July 29, 2011 at 10:05 am
Steve Jones - SSC Editor (7/29/2011)
Ninja's_RGR'us (7/29/2011)
Do you guys(als) know a way to remove the prints from the backup / restore commands?This is what I'd like to be gone from the output :
100 percent processed.
Processed 99 pages for database 'DemoRecover', file 'DemoRecover_log' on file 1.
BACKUP LOG successfully processed 99 pages in 0.037 seconds (21.836 MB/sec).
AFAIK, you cannot. These are sent along a different stream, and it's up to the client to consume them. If you return a result set, they aren't returned. Aren't these only on the messages tab?
I'm working on a PIT restore script demo and those messages are a little annoying and ad no (very little) value.
This is not 100% done but if you guys wanna have a look >>
Run this in SSMS in text mode and comment this line to be able to run the rest of the demo (runs all in 1 batch)
;
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
SET STATISTICS IO, TIME OFF
SET DATEFORMAT YMD ;
USE master ;
CREATE DATABASE DemoRecover
GO
ALTER DATABASE DemoRecover SET RECOVERY FULL ;
USE DemoRecover ;
CREATE TABLE dbo.DataLoss
(
id INT IDENTITY(1 , 1)
NOT NULL
PRIMARY KEY CLUSTERED
, dt DATETIME NOT NULL
) ;
PRINT 'Create data before FULL'
--save the times of the operations
DECLARE @dtBeforeDelete DATETIME
INSERT INTO
dbo.DataLoss ( dt )
SELECT TOP 1000
DATEADD(ms , ABS(CHECKSUM(NEWID())) % 86400000 ,
CONVERT(DATETIME , '2011-07-26')) AS dt
FROM
sys.columns C1
CROSS JOIN sys.columns C2 ;
BACKUP DATABASE [DemoRecover] TO DISK = N'Q:\Backups SQL\DemoRecover.bak' WITH
NOFORMAT , INIT , SKIP , STATS = 100 ;
PRINT ''
PRINT 'Create data after full'
PRINT ''
INSERT INTO
dbo.DataLoss ( dt )
SELECT TOP 3000
DATEADD(ms , ABS(CHECKSUM(NEWID())) % 86400000 * 3 ,
CONVERT(DATETIME , '2011-07-27')) AS dt
FROM
sys.columns C1
CROSS JOIN sys.columns C2 ;
SELECT
COUNT(*) AS RowsPerDay
, DATEADD(D , 0 , DATEDIFF(D , 0 , dt)) AS dt
FROM
dbo.DataLoss
GROUP BY
DATEADD(D , 0 , DATEDIFF(D , 0 , dt))
ORDER BY
dt
SET @dtBeforeDelete = GETDATE()
PRINT ''
PRINT '@dtBeforeDelete : ' + CONVERT(VARCHAR(23) , @dtBeforeDelete , 126)
PRINT ''
WAITFOR DELAY '00:00:01' --1 sec
PRINT 'Simulate 2 days dataloss'
SET NOCOUNT OFF
DELETE
DL
FROM
dbo.DataLoss DL
WHERE
DL.dt >= '2011-07-27'
AND DL.dt < '2011-07-29'
SET NOCOUNT ON
PRINT ''
PRINT ''
SELECT
COUNT(*) AS RowsPerDay
, DATEADD(D , 0 , DATEDIFF(D , 0 , dt)) AS dt
FROM
dbo.DataLoss
GROUP BY
DATEADD(D , 0 , DATEDIFF(D , 0 , dt))
ORDER BY
dt
BACKUP LOG [DemoRecover] TO DISK = N'Q:\Backups SQL\DemoRecover_log.bak' WITH
NOFORMAT , INIT , SKIP , STATS = 100 ;
PRINT '' PRINT ''
PRINT 'Restore the DB under a new name. The standby option let''s me query the DB without losing the ability to recover more logs later on'
PRINT '' PRINT ''
RESTORE DATABASE [DemoRestoreTable] FROM DISK =
N'Q:\Backups SQL\DemoRecover.bak' WITH FILE = 1 , MOVE N'DemoRecover' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoRestoreTable.mdf'
, MOVE N'DemoRecover_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoRestoreTable_1.LDF'
, STANDBY = N'Q:\Backups SQL\ROLLBACK_UNDO_DemoRestoreTable.BAK' ,
NOUNLOAD , REPLACE , STATS = 100
PRINT '' PRINT '';
DECLARE @selectcmd VARCHAR(1000)
SET @selectcmd = 'SELECT COUNT(*) AS RowsPerDay, DATEADD(D, 0, DATEDIFF(D, 0, dt)) AS dt FROM DemoRestoreTable.dbo.DataLoss DL GROUP BY DATEADD(D, 0, DATEDIFF(D, 0, dt)) ORDER BY dt'
PRINT '' PRINT ''
PRINT 'Confirm we''re back to the same point we were before the 2nd insert'
EXEC (
@selectcmd
)
PRINT '' PRINT ''
PRINT 'The standby mode allows me to now restore the log file(s)'
PRINT '' PRINT ''
RESTORE LOG [DemoRestoreTable] FROM DISK =
N'Q:\Backups SQL\DemoRecover_log.bak' WITH FILE = 1 , STANDBY =
N'Q:\Backups SQL\ROLLBACK_UNDO_DemoRestoreTable.BAK' , NOUNLOAD , STATS =
100
PRINT '' PRINT ''
PRINT 'Cool so now we''re back to where we were at the end of the inserts, how do we stop earlier?'
PRINT '' PRINT ''
EXEC (@selectcmd)
PRINT '' PRINT ''
PRINT 'Re-do the full backup restore'
PRINT '' PRINT ''
RESTORE DATABASE [DemoRestoreTable] FROM DISK =
N'Q:\Backups SQL\DemoRecover.bak' WITH FILE = 1 , MOVE N'DemoRecover' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoRestoreTable.mdf'
, MOVE N'DemoRecover_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DemoRestoreTable_1.LDF'
, NORECOVERY , NOUNLOAD , REPLACE , STATS = 100
PRINT '' PRINT ''
--PRINT 'I have to use dynamic SQL here for the code to run in 1 go, not necessary otherwise'
DECLARE @restorePIT VARCHAR(1000)
SET @restorePIT = 'RESTORE LOG [DemoRestoreTable] FROM DISK = N''Q:\Backups SQL\DemoRecover_log.bak'' WITH FILE = 1, STOPAT = N'''
+ CONVERT(VARCHAR(100) , @dtBeforeDelete , 126)
+ ''', STANDBY = N''Q:\Backups SQL\ROLLBACK_UNDO_DemoRestoreTable.BAK'', NOUNLOAD, STATS = 100'
EXEC (@restorePIT)
PRINT '' PRINT ''
EXEC (@selectcmd)
PRINT '' PRINT ''
PRINT 'Now ALL the data is back'
PRINT '' PRINT ''
PRINT 'Just use insert into to insert the missing data in the prod table'
PRINT '' PRINT ''
PRINT 'Cleanup, dropping both dbs and backups' ;
USE master
GO
DROP DATABASE DemoRecover
DROP DATABASE DemoRestoreTable ;
EXEC xp_cmdshell 'DEL "Q:\Backups SQL\DemoRecover.bak"' , no_output
EXEC xp_cmdshell 'DEL "Q:\Backups SQL\DemoRecover_log.bak"' , no_output
Outputs >>
Create data before FULL
Processed 176 pages for database 'DemoRecover', file 'DemoRecover' on file 1.
100 percent processed.
Processed 5 pages for database 'DemoRecover', file 'DemoRecover_log' on file 1.
BACKUP DATABASE successfully processed 181 pages in 0.060 seconds (24.652 MB/sec).
Create data after full
RowsPerDay dt
----------- -----------------------
1000 2011-07-26 00:00:00.000
995 2011-07-27 00:00:00.000
992 2011-07-28 00:00:00.000
1013 2011-07-29 00:00:00.000
@dtBeforeDelete : 2011-07-29T11:36:00.907
Simulate 2 days dataloss
(1987 row(s) affected)
RowsPerDay dt
----------- -----------------------
1000 2011-07-26 00:00:00.000
1013 2011-07-29 00:00:00.000
100 percent processed.
Processed 99 pages for database 'DemoRecover', file 'DemoRecover_log' on file 1.
BACKUP LOG successfully processed 99 pages in 0.037 seconds (21.836 MB/sec).
Restore the DB under a new name. The standby option let's me query the DB without losing the ability to recover more logs later on
100 percent processed.
Processed 176 pages for database 'DemoRestoreTable', file 'DemoRecover' on file 1.
Processed 5 pages for database 'DemoRestoreTable', file 'DemoRecover_log' on file 1.
RESTORE DATABASE successfully processed 181 pages in 0.035 seconds (42.261 MB/sec).
Confirm we're back to the same point we were before the 2nd insert
RowsPerDay dt
----------- -----------------------
1000 2011-07-26 00:00:00.000
The standby mode allows me to now restore the log file(s)
100 percent processed.
Processed 0 pages for database 'DemoRestoreTable', file 'DemoRecover' on file 1.
Processed 99 pages for database 'DemoRestoreTable', file 'DemoRecover_log' on file 1.
RESTORE LOG successfully processed 99 pages in 0.041 seconds (19.705 MB/sec).
Cool so now we're back to where we were at the end of the inserts, how do we stop earlier?
RowsPerDay dt
----------- -----------------------
1000 2011-07-26 00:00:00.000
1013 2011-07-29 00:00:00.000
Re-do the full backup restore
100 percent processed.
Processed 176 pages for database 'DemoRestoreTable', file 'DemoRecover' on file 1.
Processed 5 pages for database 'DemoRestoreTable', file 'DemoRecover_log' on file 1.
RESTORE DATABASE successfully processed 181 pages in 0.040 seconds (36.979 MB/sec).
100 percent processed.
Processed 0 pages for database 'DemoRestoreTable', file 'DemoRecover' on file 1.
Processed 99 pages for database 'DemoRestoreTable', file 'DemoRecover_log' on file 1.
RESTORE LOG successfully processed 99 pages in 0.035 seconds (23.083 MB/sec).
RowsPerDay dt
----------- -----------------------
1000 2011-07-26 00:00:00.000
995 2011-07-27 00:00:00.000
992 2011-07-28 00:00:00.000
1013 2011-07-29 00:00:00.000
Now ALL the data is back
Just use insert into to insert the missing data in the prod table
Cleanup, dropping both dbs and backups
July 29, 2011 at 11:13 am
Jeff Moden (7/29/2011)
Gosh... has anyone ever heard of a good reason to have two identically named SQL Server instances in the same Domain? My gut says there isn't one and except (possibly) for the very brief period of bringing a new system online, it should never be done. Could someone that knows for sure (I just don't know and would actually like to know the answer for the future) respond to the following thread with either a "yay" or "nay" reason? The link takes you to my very sincere but totally unqualified response...http://www.sqlservercentral.com/Forums/FindPost1150874.aspx
Thanks folks.
I think you guys have covered it out there, but I can't imagine instance names being a problem if server names are different. If two servers in the domain have the same name, you are probably f***ked regardless of instance names, unless you have some very magical DNS configuration (and having that would probably be a deplorable mistake).
Tom
July 29, 2011 at 11:23 am
Steve Jones - SSC Editor (7/29/2011)
Interesting rant here: http://www.insanit.net/technology/smartphone-rants-3-what-do-i-want-a-real-windows-smartphone-when-do-i-want-it-now/Anyone else see the need/value here? I'm struggling to see if this is something that has value.
Give me a phone with 8GB RAM, 600GB hard drive (or, preferably, SSD), WIndows 7, 4 core 2.8GHz 64 bit CPU, that will fit in my shirt pocket,so that I don't have to lug a great big heavy laptop about with me; and then I wil be very happy.
If mobile phone vendors (particularly Apple) had not pretended that mobile phones have useful computing capability, or are decent general purpose computers, maybe I wouldn't have come to desire decent shirt pocket computing. But the y didn't stop, the continue to lie to us, and I have acquired that desire.
Tom
Tom
July 29, 2011 at 11:36 am
Tom.Thomson (7/29/2011)
Steve Jones - SSC Editor (7/29/2011)
Interesting rant here: http://www.insanit.net/technology/smartphone-rants-3-what-do-i-want-a-real-windows-smartphone-when-do-i-want-it-now/Anyone else see the need/value here? I'm struggling to see if this is something that has value.
Give me a phone with 8GB RAM, 600GB hard drive (or, preferably, SSD), WIndows 7, 4 core 2.8GHz 64 bit CPU, that will fit in my shirt pocket,so that I don't have to lug a great big heavy laptop about with me; and then I wil be very happy.
If mobile phone vendors (particularly Apple) had not pretended that mobile phones have useful computing capability, or are decent general purpose computers, maybe I wouldn't have come to desire decent shirt pocket computing. But the y didn't stop, the continue to lie to us, and I have acquired that desire.
Tom
There used to be a pocket computer like that, but without phone capabilities. I don't know why phones aren't more like full-fledged computers at this point.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 29, 2011 at 12:16 pm
Steve Jones - SSC Editor (7/29/2011)
Interesting rant here: http://www.insanit.net/technology/smartphone-rants-3-what-do-i-want-a-real-windows-smartphone-when-do-i-want-it-now/Anyone else see the need/value here? I'm struggling to see if this is something that has value.
Not really. Problem is screen size & input mechanisms. I really, really, don't want to sit around editing books on a keyboard that my thumbs can cover. I'm absolutely not going to do PowerShell development or TSQL development on a 3" screen. Ain't happening. So it's not a question of processing power or operating system. It's what you can do with the medium. Fact of the matter is, I'm more impressed with the combined ops aspect of the phone that I've been getting with the Droid. Interested in a bottle of wine, open up SpringPad, scan the bar code, done. Data entered and you can access that from your desktop later to order from a supplier.
I think the guy is off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2011 at 12:22 pm
Grant Fritchey (7/29/2011)
Steve Jones - SSC Editor (7/29/2011)
Interesting rant here: http://www.insanit.net/technology/smartphone-rants-3-what-do-i-want-a-real-windows-smartphone-when-do-i-want-it-now/Anyone else see the need/value here? I'm struggling to see if this is something that has value.
Not really. Problem is screen size & input mechanisms. I really, really, don't want to sit around editing books on a keyboard that my thumbs can cover. I'm absolutely not going to do PowerShell development or TSQL development on a 3" screen. Ain't happening. So it's not a question of processing power or operating system. It's what you can do with the medium. Fact of the matter is, I'm more impressed with the combined ops aspect of the phone that I've been getting with the Droid. Interested in a bottle of wine, open up SpringPad, scan the bar code, done. Data entered and you can access that from your desktop later to order from a supplier.
I think the guy is off.
Funny, I would have been in his camp until a few weeks ago when I got my first smartphone myself. Totally with you guys, as much fun as it is to be able to view the real intertubes on the phone, the well-designed mobile sites are MUCH easier, because they're sized for the input. That guy said he wants Photoshop/GIMP - what the heck would you do with that on a phone?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 28,531 through 28,545 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply