http://www.sqlmag.com/authors/author/author/5611427/rsscomment/5611427en-USFri, 25 May 2012 08:15:15 GMTFri, 25 May 2012 08:15:15 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorFri, 16 Sep 2011 04:02:31 GMT
Hi, nice tool. Still some things that should get improved: - Strings should get filtered away. A SELECT 'This is no comment /* begin' FROM... will cause the code to crash. - If the code finds SELECT Currency FROM BigTable, it will record a reference to the table Currency, although in this code it's just a field name. - very slow, can run on a slow server for days until one database is scanned through - some references are missed, not sure why]]>Eric BauersachsFri, 16 Sep 2011 04:02:31 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorhttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorWed, 19 Nov 2008 03:40:53 GMT
Also see http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx]]>mariusWed, 19 Nov 2008 03:40:53 GMThttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 03 Oct 2008 14:30:59 GMT
Interesting article and thanks for at least opening the subject. I do believe that the 1st parameter (@SourceFileGroupID) is a bit redundant since you can determine it from SysIndexes inside the script. What happens if the source PK and Indexes were created on two different file groups? The script is doing a check against the @SourceFileGroupID parameter and I guess it will omit the ones that are not on the same group as the parameter. I believe that determining each object’s (heap, PK or IDX) file group from SysIndexes would be a better approach. My question is if someone has tried to move partitioned tables to new file groups, I’m working on it right now and this is how I came across this article, any tips are welcome. BTW, the SysIndexes has NULL in the GroupID field for partitioned objects and sp_HelpFileGroup does not return anything about the Partition Scheme, the closest thing I was able to find is "Select * From sys.Data_Spaces". Any help/comment is appreciated.]]>CALINFri, 03 Oct 2008 14:30:59 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorWed, 24 Sep 2008 16:25:42 GMT
(similar change for a.freq_subday_type = 4 above in the script.) -- Finally, we do the same as above for all jobs that run every Y hours. SET @i = 0 SET @Cnt = 1 WHILE @Cnt > 0 BEGIN INSERT INTO #JobScheduledDatesAndTimes (job_id, schedule_name, RunDateTime) SELECT b.job_id, b.schedule_name, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) FROM #JobScheduleInfo a INNER JOIN #JobScheduledDates b ON a.job_id = b.job_id AND a.schedule_name = b.schedule_name WHERE a.freq_subday_type = 8 -- We need to make sure that we don’t cross over to the next day. AND DATEPART(day, DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate)) = DATEPART(day, b.RunDate) --and the time is whithin the daily interval (Gabriela Nanau) AND replace(convert(varchar(8),DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate),114),’:’,’)<= REPLICATE(’0’, 6 - LEN(LTRIM(RTRIM(CAST(active_end_time AS VARCHAR(6))))) ) + LTRIM(RTRIM(CAST(active_end_time AS VARCHAR(6)))) -- Also consider the min/max_datetime_to_consider AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) >= a.min_datetime_to_consider AND DATEADD(ss, @i*3600*a.freq_subday_interval + b.StartTimeInSecFromStartOfDay, b.RunDate) <= a.max_datetime_to_consider SET @Cnt = @@ROWCOUNT SET @i = @i + 1 END]]>GabrielaWed, 24 Sep 2008 16:25:42 GMThttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorhttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorWed, 24 Sep 2008 16:22:04 GMT
Great solution and a lot of work! I had this kind of script on my to-do list for some time but I never found the time to start working on it. So here comes your script and saves the day! I made a small adjustment to JobScript_FindMissedJobs.sql, so that for jobs that run several times in a day will also consider the maximum time during the day. So if a job is scheduled to run daily, every hour between 2:00 Am and 22:59:59 PM it will not consider a valid time to run 23:00:00 (step # 3, please see below my comment (Gabriela Nanau). Another thing I would mention is that because the values in field FirstJobRunAfterMissedSchedule come from sysjobhistory this date is as accurate as sysjobhistory. If the interval considered for analyisis is long enough and the job history was overwritten by newer events the value in FirstJobRunAfterMissedSchedule might not necessarily be the first run after the schedule, but the first one still available in the history. As long as the studied interval don’t go before the oldest record in job jistory, the script works fine. Once again, this is a good script and provides a lot of help for a DBA! Congratulations!]]>GabrielaWed, 24 Sep 2008 16:22:04 GMThttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorhttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorThu, 04 Sep 2008 11:19:21 GMT
Good and deep aproach to a real problem, this is the type of article that I look for from time to time, I like to know the does and doesn’t of SQL Server and the impossibility to track the jobs is a large doesn’t... Thank you for sharing your code....]]>MarcosThu, 04 Sep 2008 11:19:21 GMThttp://www.sqlmag.com/article/tsql3/plugging-the-gaps-in-sql-server-job-tracking#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorThu, 21 Aug 2008 10:19:47 GMT
pjstreiff - you are correct. In its current form, the script is not schema-aware. However, if you follow the script and check for where and how the variable @TableToMove is used, you’ll see that you can set @TableToMove = ’MySchema.MyTable’ (for example) and then you should be able to make very easy modifications in the code wherever @TableToMove is not schema-aware. Hope this help.]]>meganbearly Thu, 21 Aug 2008 10:19:47 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorMon, 18 Aug 2008 20:19:37 GMT
Omri: Thanks for this great script! I need to specify schema name in @TableToMove, however, the script seems to be schema unaware as it errors when I try to include [del.TableA]. I have 3 TableA’s under 3 different schema’s. I’m afraid if I don’t specify schema, then the script will try to move dbo.TableA, which is not the table I need to move. Is there any way around this limitation, or am I missing something?]]>PhilMon, 18 Aug 2008 20:19:37 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorMon, 11 Aug 2008 13:59:28 GMT
The procedure works for procedure but with my instance (SQL2005 SP2) doesn’t with views. Everytime i receive the error Syntax not correct near ’AS’]]>infante@gigasrl.it Mon, 11 Aug 2008 13:59:28 GMThttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorWed, 20 Feb 2008 09:51:40 GMT
If you headline the article "User this nifty code..." why don’t you give us the code???]]>LesWed, 20 Feb 2008 09:51:40 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorWed, 06 Feb 2008 17:22:15 GMT
I couldn’t see actual code, only thing I can download is 97018.zip file and it has only sample code to created tables, DBs, filegroups etc. Where is the actual code to move tables between file groups?]]>PrabhuWed, 06 Feb 2008 17:22:15 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 25 Jan 2008 13:28:13 GMT
msl9669 - What do you mean by Object References? Not sure I understand the comment...]]>OmriFri, 25 Jan 2008 13:28:13 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 18 Jan 2008 16:30:13 GMT
I have to rate this article as a 3 under less useful. First and foremost, object references are SQL Server 2000. Please... I do give Omri credit for emphasizing the significance of dropping nonclustered indexes before rebuilding the clustered. That’s about it.]]>MichaelFri, 18 Jan 2008 16:30:13 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorWed, 16 Jan 2008 18:10:49 GMT
download link http://www.winnetmag.com/Files/09/97018/97018.zip is not working...]]>FrankWed, 16 Jan 2008 18:10:49 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorTue, 15 Jan 2008 14:17:55 GMT
To jcasement, Are you referring to a script in addition to the code in the download files? Thanks, Diana]]>DianaTue, 15 Jan 2008 14:17:55 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorThu, 10 Jan 2008 11:16:15 GMT
Nice article but useless without the script -- I already do these types of manual moves and having a script would be helpful.]]>JAMESThu, 10 Jan 2008 11:16:15 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorWed, 09 Jan 2008 09:50:22 GMT
Thank you for all the replied. I wanted to reply to all the posted comments: tangojam (first posting) - SQL Mag was notified of the missing script and I hope they fix it promptly. The script can also be downloaded on SQL Farms’ (free) forums at: http://education.sqlfarms.com/education/ShowForum.aspx?ForumID=20 (requires registration). ColdFusion74- Thank you for the comment. I wasn’t able to find how to move tables between filegroups in SSMS. If there is such a capability - please share it with everyone and post here. tangojam (second posting) - you are absolutely correct; you can drop all non-clustered indexes and then recreate the clustered index using the DROP_EXISTIG clause, and then recreate the non-clustered indexes. One note on this- it has to be done carefully to account for unique indexes/constraints/keys as well. There are many cases to be tested. The provided script does take all cases into consideration.]]>OmriWed, 09 Jan 2008 09:50:22 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorMon, 07 Jan 2008 11:44:05 GMT
regarding the performance hit of using DROP_EXISTING when rebuilding the clustered index to move the file group: if you are going to use CLUSTERED INDEX ... DROP_EXISTING, then drop the non-clustered indexes before issuing the command. then you only have to re-create the non-clustered indexes once.]]>jamesMon, 07 Jan 2008 11:44:05 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 04 Jan 2008 10:55:35 GMT
Yeah... It would be not bad to add script itself. Someone may have practical interest in moving tables between filegroups... Although, article is very useful because summaries information about this problem. Also, there is a mistake - " Unfortunately, the ability to move a table to a different filegroup from the UI is no longer available in SQL Server Management Studio." I believe that this ability exists.]]>AlexeiFri, 04 Jan 2008 10:55:35 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 04 Jan 2008 10:45:09 GMT
The article doesn’t explain about what the novel method adopted in the "A More Efficient Solution" approach, besides the script is missing.]]>BalajiFri, 04 Jan 2008 10:45:09 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorThu, 03 Jan 2008 10:36:12 GMT
I don’t see the actual script you mention to do the moving in the zip file. thanks!]]>jamesThu, 03 Jan 2008 10:36:12 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorWed, 02 Jan 2008 13:50:53 GMT
Ken- TEXTIMAGE_ON was not supported in SQL 2005 RTM and I’m not sure about SP1 (to the best of my knowledge). I looked again at the docs for the latest release (updated 2/2007) and it looks as if it is now supported. You are absolutely correct- it is currently supported so this is my mistake. I’m not sure when this support was added to SQL 2005. Would be interesting to find out. I’ll try to find more info... thank you for the input! Very good comment.]]>OmriWed, 02 Jan 2008 13:50:53 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorFri, 28 Dec 2007 10:17:42 GMT
I use the TEXTIMAGE_ON clause in SQL Server 2005 all the time. Am I missing something here? The article says it is no longer available in SQL 2005. -Ken]]>KENFri, 28 Dec 2007 10:17:42 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorThu, 20 Dec 2007 15:30:32 GMT
The link to download the code is a broken link. When I click it, it tries to go to http://www.winnetmag.com/Files/09/97018/97018.zip , and I get the message: "Sorry the page you are trying to reach is temporarily unavailable or the page no longer exists."]]>GeorgeThu, 20 Dec 2007 15:30:32 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-tables-between-filegroups-a-better-way#commentsAnchorhttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorTue, 23 Oct 2007 15:49:27 GMT
Excelent. Thanks]]>OscarTue, 23 Oct 2007 15:49:27 GMThttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorhttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorFri, 19 Oct 2007 09:13:54 GMT
roni6124, There are many different applications and utilizations that can be achieved by querying sysprocesses. The code in the article was provided to give readers a first introductory step to the table/view and the concept of kicking users out. Blocking processes is one reason why you would want to kick users out, as you correctly mention. It is pretty simple to alter the provided code to perform this task, by using the SQL statement you suggested. Other solutions for different problems can be achieved using supplemental code. I thank you for your input! Omri.]]>OmriFri, 19 Oct 2007 09:13:54 GMThttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorhttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorFri, 19 Oct 2007 09:10:49 GMT
Sid, You are correct. There are several levels of complexity when discussing dependencies: 1. Object dependencies inside a single database. 2. Cross-DB object dependencies (on the same server). 3. Cross-DB and Server dependencies (e.g., over linked server, RPC calls, OPENROWSET commands, etc.). 4. Dependencies between a middle-tier or application tier and the database objects. As mentioned in the article, the proposed solution was written to address #1. The other #2-#4 dependency types are beyond the scope of this article. Again- you are absolutely correct. The article indeed does not address cross-DB dependencies. Omri.]]>OmriFri, 19 Oct 2007 09:10:49 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorhttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorFri, 19 Oct 2007 09:10:30 GMT
Mordechai, In SQL Server 2005 Microsoft introduced the procs sp_refreshview (for views) and sp_refreshsqlmodule for other database object types. The sp_refreshview does prompt the side effect of updating dependencies of objects related to the view and not only the view; similarly with sp_refreshsqlmodule. To the best of my knowledge, both techniques indeed prompt the recompilation of the object since SQL Server updates the system meta-data related to these objects (thus recompilation is inevitable). Hope this helps. Omri.]]>OmriFri, 19 Oct 2007 09:10:30 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorhttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorThu, 18 Oct 2007 11:43:29 GMT
This article doesn’t say how sys.sysprocesses table should be queried to return the sid(s) and spid(s) that are causing trouble. In a situation where a single or a bunch of queries are bringing pruduction down to a halt you have little resources/time available to figure this out. Sometimes the chain of blocks are so severe that you can’t start a query window. We use SQLCMD -A to connect and query the sys.sysprocess where blocked != 0 and kill the offending the process. Aside from not addressing the issue of identifying SIDs and SPIDs, I think the KickUserOut.sql is a nice and creative tool. Other DBAs might find method in this article useful. I didn’t.]]>RONThu, 18 Oct 2007 11:43:29 GMThttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorhttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorWed, 03 Oct 2007 13:25:42 GMT
Very nice but the SQl2K still does not work for dependencies in cross-databases. I have code like the following: --openquery to Dtitletemp for CAE match exec (" insert into " + @TDBtemptbl + " (" + @TDBtempflds + ") select " + @TDBtempflds + " from openquery(" + @acServer + ", ’select " + @TDBtempflds + " from Dfrndis.dbo.vwArwEntitled where " + @TDBwhereIN + " ’) ") and the table in the from clause was not found Sid]]>JAMESWed, 03 Oct 2007 13:25:42 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorhttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorFri, 24 Aug 2007 09:40:23 GMT
Omri has done an exceptional job of providing necessary detail to this article.]]>THOMASFri, 24 Aug 2007 09:40:23 GMThttp://www.sqlmag.com/article/tsql3/kick-a-user-out-of-a-database-in-just-2-steps#commentsAnchorhttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorMon, 13 Aug 2007 05:36:01 GMT
I found a bug in the 2005 version: The line that reads PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1))) should read PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000)) because the third parameter to the subsctring is length not position]]>MichaelMon, 13 Aug 2007 05:36:01 GMThttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorhttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorFri, 10 Aug 2007 05:48:44 GMT
Hi I was recently told that there is a way to update information in sysdepends table. (but they did not tell me how). I am assuming that they did not mean recopiling all SPs. Is there a way to that?]]>MordechaiFri, 10 Aug 2007 05:48:44 GMThttp://www.sqlmag.com/article/tsql3/a-dependency-checker-you-can-depend-on#commentsAnchorhttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorWed, 25 Jul 2007 13:34:33 GMT
Disregard the above, I fat fingered.]]>PeterWed, 25 Jul 2007 13:34:33 GMThttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorhttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchorWed, 25 Jul 2007 13:34:11 GMT
The code does not work. It only returns Null when using the 2000 code on a 2000 machine.]]>PeterWed, 25 Jul 2007 13:34:11 GMThttp://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects#commentsAnchor