InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 18:26:33 GMT20http://www.sqlservercentral.com/Forums/Topic1465427-1550-1.aspxI am using below query to get all the permissions of Old user. here my question is i am getting only database query in result set. i need script for all the databases.SET NOCOUNT ONDECLARE @OldUser sysname, @NewUser sysnameSET @OldUser = 'Old'SET @NewUser = 'New'SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') FROM sys.database_role_members AS rmWHERE USER_NAME(rm.member_principal_id) = @OldUserORDER BY rm.role_principal_id ASCSELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'FROM sys.database_permissions AS perm INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id] INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id LEFT JOIN sys.columns AS cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_idWHERE usr.name = @OldUserORDER BY perm.permission_name ASC, perm.state_desc ASCSELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) + perm.permission_name + SPACE(1) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'FROM sys.database_permissions AS perm INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_idWHERE usr.name = @OldUserAND perm.major_id = 0ORDER BY perm.permission_name ASC, perm.state_desc ASCthank you in advance.Wed, 19 Jun 2013 16:45:13 GMTAAKRhttp://www.sqlservercentral.com/Forums/Topic1464922-1550-1.aspxWe have a sharepoint farm that uses a cluster for database server.The cluster has two nodes, one active the other is passive.The database server now is SQL 2008 SP1, and sharepoint is Sharepoint 2007(MOSS 2007) SP2.Now I would like to apply SQL server 2008 SP3 on the cluster - two nodes.I have experiences to apply Service pack, but this one has some complications. First it is a cluster, second it is a sharepoint cluster that we don't have much control of their databases.I never did this before, and we donot have a test environment.I did find a knowlegebase article 958734 in microsoft talking about failover cluster rolling patch but it sounds very complicated.We only have two nodes, so I don't feel comfortable with following the article:I asked the original person who installed the cluster, he mentioned the SQL instances are installed first on Node1, then add to Node2.here is what I plan to do after some research online:1. Apply the hotfix on pasive node N22. Reboot the passive node N23. Failover on SQL resource : the passive node become the active node4. Apply the hotfix on the passive node N15. Reboot the passive node N16. Actually I want to use N1 as active node, so failover to N1 again to make N1 active node.My question is :Is that all I need to do?For N2, what is exactly do on the internal update, will it only do SP for client since it is an added node?Anyone has similar setup for sharepoint database, can you share some experience of apply sp on the the nodes? What I need to pay attention to?Actually we don't care too much about down time, so any way that can help do the SP safely should work for us.Thanks,Tue, 18 Jun 2013 17:59:21 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1465404-1550-1.aspxI'd like now how to better understand the memory utilize by sql server. I have done some continues statistics and using monitoring tools and it seems that in some of my servers I see a constant high memory usage for both the OS and the SQL Server memory. In some instances the sql server memory reaches 95% and stays to that under for several hours.Any ideas where I can start reading and understanding what it all means and how to figure out what's causing the high memory usage?Thank you.Wed, 19 Jun 2013 15:27:51 GMTHildaJhttp://www.sqlservercentral.com/Forums/Topic1465399-1550-1.aspxIn windows control-panel , uninstall and change a program, I see SQl server 2008 service pack 2 is installed, but also see there is a separate SQL server 2008 service pack 2 browser.I am going to apply service pack 3 to fix a SQL browser issue, I was told the SP3 will fix that.My question is by running SP3 will also upgrade SQl browser, correct?Thanks muchWed, 19 Jun 2013 15:16:25 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1464661-1550-1.aspxI have an issue with some of my jobs. They are owned by the same domain account that the SQL Server Agent uses. When they run as scheduled everything appears fine. When they are run manually, we occasionally get feedback that they are running under the SA account.I'm scratching my head on this one trying to figure out why they run under the proper account when scheduled but sometimes (not always) run under SA when manually executed by a member of the DBA team.Books Online is not helpful and my google-fu does not seem to come up with articles pointing out the authentication routines of the SQL Agent jobs. Does anyone have links they can point me to that might explain this issue?EDIT: More information.The agent is using an account assigned to the SysAdmin role (I don't control this) and there are no proxy accounts being used on any of the job steps.We are getting the information from a login watch job that looks at master.dbo.sysprocess and gets the login information from that table.So what process is Microsoft using to load the sysprocesses table? I'm wondering if that might hold a clue as to why we're seeing SA as the account when a job is manually run.Tue, 18 Jun 2013 07:49:55 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1464921-1550-1.aspxI have a problem I am trying to solve. I would like to run a query of some sort that will compare a list of active databases on an instance of SQL and compare it to the backup history. If a full backup exists for that db, then ignore it. However, if a FULL backup does NOT exist for that db, then perform a FULL backup of that db.So, in simpler form:If the database has a FULL backup, do nothingIf the database does NOT have a FULL backup, take a FULL backup of that db.Any help would be appreciated. I can find several excellent scripts here that will check to see if a db has a backup...but nothing that will allow me to compare and then perform a backup. I'm not sure how to put all of it together.Thanks!Tue, 18 Jun 2013 17:52:38 GMTsandtoad2http://www.sqlservercentral.com/Forums/Topic1465341-1550-1.aspxDays by day i am learning something new in SQL Server. Before learning anything i want to understand why do we need this feature. This is the same case when i am learning SQLCMD.I would like to know what are the thing that can be done through SQLCMD which are not possible through SSMS.Or are there any advantages that while doing a certain set of task through SQLCMD.Thanks in advance.Wed, 19 Jun 2013 12:59:51 GMTShadab Shahhttp://www.sqlservercentral.com/Forums/Topic1465318-1550-1.aspxHi,There were lots of PAGEIOLATCH_SH waits while running queries, when drilled down I found the statistics were out of date, hence I ran update statistics to all databases. That time usage of physical memory became high and it hold almost full (63.6 GB out of 64 GB). Now Update Statistics activity has been finished and all the queries respond very fast but the usage of physical memory remains 63.6GB only. The Windows Task Manager->Processes->sqlservr.exe shows it holds only 961,360 KB of Memory. Other processes also don't take much memory. It is a Production server - I can't restart. Hence please help me to free up space.ThanksBala Wed, 19 Jun 2013 12:05:32 GMTbalasaukrihttp://www.sqlservercentral.com/Forums/Topic1464917-1550-1.aspxAny one knows how to uninstall a service pack of SQL server 2008 Sp3?ThanksTue, 18 Jun 2013 17:41:51 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1461174-1550-1.aspx:unsure:Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything regarding this issue;An error was logged in the Cluster and Event Log, saying Event ID:1609 Error:0x80071397 "The operation failed because either the specified cluster node is not the owner of the resource, or the node is not a possible owner of the resource"i have done all need to be done, but couldnot get the IP address online manually.its a 4 node cluster, 2 acts as primary and other 2 as secondrary,We have 5 applications over 4 depends on one node of primary and the left one on the other node.Rest two nodes remain as DR in case of failover.there are 2 IP Address for each node. In which one always remain online, the never respond to PING atleast, but one of them does respond but never comes online on selecting the option "Bring thus resource to online" How can i bring this IP address online. Please find all the work i have done on this issue.As i found few sujjestion from friends and also google, i Tried all of them resulting no solution.Suggestion 1: Restart the servers.ATTEMPT 1: I have shifted all the application to node 2 from node1, Restarted the NOde 1, Done the same with the node 2; Result: Nothing.Suggestion 2: Try from all the nodes to bring the IP address online.Attempt 2: Tried, no use. Not even one got turned in.On pinging any of the IP Address except 1 does show timed out, the exceptional IP Address does respond, but i dont know why its not getting online when requested.Suggestion 3: I found a post in some forum saying that, the IP Address from node which holds the quorum does only gets online, rest remain in offline.Attempt 3: said to manager the same thing, he suggested me to work more on it. Suggestion 4: Check subnet mask,Attempt 4: Checked every Subnet mask, does remain similar. but i could never make those IP address online from offline.BUT: when i was working on this matching the subnet masks; I found all the IP Address in different nodes, except the the IP's which i am not able to make them online, Including the one which got pinged success.I found another thing over here, I check the IPconfiguration in all the nodes, pinged all the IP's in all the nodes, including IP's which are online, The IP's which are shown online does ping in all the nodes (which is common)But the IP's Which are offline doesnt ping with the nodes of Primary and on pinging those on secondary nodes they doesnt reported as "timed out" but "Host not reachable"Does this sign anything, i haven't found anything on this, when i searched.When i report the same thing to my manager and try to involve Networking and Windows Admins in to it. He said gonna check it next monday.. I havent found perfect solution till now, i was working on this since 3 days, done a lot of googling, but could not find it any where. Hope i gave all the details. If any one had already faced such kind of issue, Please expalin me wat need to be done, Also let me know am i in right path for the solution,CAN ANY ONE HELP ME ON THIS...Please note***ROOKIE:hehe:*** hereFri, 07 Jun 2013 11:52:43 GMTrajborntodarehttp://www.sqlservercentral.com/Forums/Topic1464452-1550-1.aspxYears ago we installed a sql server service pack on a non-clustered server and the results were usually fine. Now they want a fallback plan in case the service pack install fails. This is a good idea but I'm not sure what comprises a good fallback plan for sql. (Uninstall the service pack -- never done that before. We can start by backing up all databases but restoring them, including the system databases, will be time consuming. Maybe we should image the entire server to trim the restoration time.)Any thoughts you have on this matter will be appreciated.TIA,BarkingdogP.S. I presume a sql service pack can possibly update\modify both system and user databases.Mon, 17 Jun 2013 22:40:50 GMTBarkingdoghttp://www.sqlservercentral.com/Forums/Topic1147295-1550-1.aspxHi Folks,i tried to edit a job step from ssms and am getting the following error.i've attached the screenshot of the error, please kindly help me how to fix this error..TIA..Sun, 24 Jul 2011 22:40:13 GMTMasterDBhttp://www.sqlservercentral.com/Forums/Topic1464790-1550-1.aspxI've got a database with a tran log that expanded and then filled up the disk. Unfortunately, I can't even execute "use database" without an error (cannot be opened due to insufficient disk). If you can't "use" the database, then you can't alter the thing to set it offline then online to fix the problem. And if you can't "use" the database then you can't truncate the log via dbcc shrinkfile (databaseName_Log, 1) -- shrink truncated log file to 1 megSometimes your sys admin can expand the disk the log is on. If that is not possible, how can I bring this database back online? There's always a restore from the latest backup, but I'm wondering if there is any other viable solution.Tue, 18 Jun 2013 10:52:16 GMTaiki4ever-796329http://www.sqlservercentral.com/Forums/Topic1464788-1550-1.aspxI have a vendor's production MicroTel.It has 3 databases. They are all at bulk-logged recovery mode.I setup a maintenanance plan to do full at weekend and every day a differential, and every 3 hours a transaction log backup.I got a failed error:BACKUP LOG cannot be performed because there is no current database backup.I do see it has a full backup and a differential before the transaction log backup, why it said no current backup?I can also verify it has a full backup by using restore the database from SSMS, I see it listed the most recent full back up and differential backup.THanks,Tue, 18 Jun 2013 10:46:51 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1464718-1550-1.aspxIs it possible to restore a database's logical path to multiple physical files?The following produces an expected file or id error message.RESTORE DATABASE TestDB FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf', 'C:\MySQLServer\testdb2.ndf', MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf';GOTue, 18 Jun 2013 09:05:17 GMTmarks_262http://www.sqlservercentral.com/Forums/Topic1464464-1550-1.aspxHi,I need to stop a particular event "Error: 1105, Severity: 17, State: 2.Could not allocate space for object 'dbo.XXX'.'XXXXX' in database 'XXXXX' because the 'PRIMARY' filegroup is full" writing in SQL SErver errorlog.Could you please let me know with the trace number which I need to use to stop the warning writing in SQL Server errorlog?Tue, 18 Jun 2013 00:19:50 GMTKumar-386306http://www.sqlservercentral.com/Forums/Topic1464417-1550-1.aspxI'd like to get an idea of what is normal when it comes to the page life expectancy. I was reading that on an average server the PLE is about 300 or 5 minutes. Some of my database servers have PLE of 2244, 74252 and 6707. should I worry about this hight numbers?I'm using the query below to pull this information.SELECT [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'Mon, 17 Jun 2013 15:53:02 GMTHildaJhttp://www.sqlservercentral.com/Forums/Topic1405978-1550-1.aspxI'm trying to track down the job_id for a job that sent an auto-email alert to us this morning. The information I have is:[quote]The host SERVERNAME has logged in with the functional login 'sa' using SQLAgent - TSQL JobStep (Job 0x3FF647C915B5604EAAAEBB0385D81250 : Step 1).[/quote]The number doesn't equate to a UNIQUEIDENTIFIER or any Job_ID in sysjobs. Does anyone have advice on how I can make the connection?Fri, 11 Jan 2013 06:48:50 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic1462675-1550-1.aspxHello,I have had an issue on a SQL and SSRS server that i have been unable to resolve without getting someone to restart the application services to drop and recreate the TCP connections made to SQL. I have used Quest to look into this but can find very little apart from which SPID it is and where it is coming from.We are using WebSense and on the server there is one TCP connection of concern, that at the start, has a memory allocation of 2....and 10 days since that restart....it is 474,849....which equates to around 3.8GB. We have a MIN and MAX memory setting of 4.8GB.Currently we have 3MB for the procedure cache when it was originally just under 2GB....and 1GB for the buffer cache where it was originally just under 2GB also. This is not a heavily used server so luckily the performance impact is minor. However, since yesterday the alerts for recompilation (all <40%) are driving me crazy!! The procedure cache is shrunk all the way down....and once the procedure cache is down to its smallest, the buffer cache starts to suffer and lose its allocation.I refuse to stop the alerts on this server just to make it quiet. I have checked the DBCC MEMORYSTATUS which gives very little information that i have already. The MEMORYCLERKS DMV shows 3.7mil [b]single-pages[/b] for SQLCONNECTIONPOOL. ReportingServices is only using 150MB and doesnt seem to be used much anyway.I have logged a call with WebSense but they dont seem to have any idea what could cause this. I was hoping someone here could point me in the right direction or to assist in trying to get more imformation, and more importantly, a way to show WebSense, or myself, the cause of the issue and hopefully resolve it.I have read that something using prepared statements can cause a memory leak....but unsure how to find any evidence for this.The server details are:Windows 2008 R2 SP1SQL 2008 SP3 Enterprise (10.0.5500)2 x vCPU8GB vRAMI saw another post relating to a similar issue but no answers. My memory usage graph is a dead straight line with no deviations....meaning the consumption was constant. I would not expect the WebSense logging utility to be as busy during the day as at night so was thinking this was not related to the SQL activity....more along the lines of the type of connection or something in the application....but again.....how do i show this?Any help would be much appreciated.Regards,SamuelWed, 12 Jun 2013 08:50:56 GMTlilywhiteshttp://www.sqlservercentral.com/Forums/Topic1464124-1550-1.aspxHi Experts,I want to monitor disk space of all servers and i have created a centralised server from where i can check all the server disk space at once using xp_fixeddrives. How can i make it as a scheduled job and any script to show servers having least disk space first?Thanks in Advance.Mon, 17 Jun 2013 06:58:14 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1449064-1550-1.aspxHi guys, Recently we got this error:The database principal owns objects in the database and cannot be dropped.The database version is SQL Server 2008,I have checked that user doesn't have schema,procedure,function,table,view and so on.My question is: how to check the user's objects?Fri, 03 May 2013 00:57:52 GMTzhazhuzhaohttp://www.sqlservercentral.com/Forums/Topic1462009-1550-1.aspxHi there, I've been trying to get some security defined around SQL Agent to a point I'm happy with the controls in place but allowing my developers to do their work without too many restrictions (aka needing my intervention :-D). I've hit an obstacle and I'm not sure of how to get around it, please help! :-)I have three environments - DEV, UAT & PRD. I have granted my developers sysadmin on DEV, no worries there. Moving to UAT, I have granted them SQLAgentOperator role so they have the ability to edit / enable / execute jobs. In PRD, security is stripped back further in that a job needs to be owned by sa to run (i.e. if something gets loaded up, it won't run until I've reviewed it and no one can amend it either). In UAT I have created a proxy account, permissions granted to a database role I've created in msdb, which my developers have been added to, that then allows them to run SSIS jobs (SQL Server doesn't allow you permission to run) by using the runas selection in the jobstep.The issue is around UAT. We generally work in a team, so different people will be working on the same piece of work at different points. The SQLAgentOperator role only allows you to edit jobs in your own name. SQL Server won't allow you to add a group as a job owner and therefore I'm a little stuck, if user A has created a job, then user B wants to edit it, I still need to intervene in order to change the owner (since ownership change is reserved for sysadmin).So how do I get around this? Is this by design? One way I've considered solving this is by granting their secondary ("admin") accounts sysadmin permissions, but ideally I'd like a neater solution.Tue, 11 Jun 2013 05:38:41 GMTJJB@TGThttp://www.sqlservercentral.com/Forums/Topic1463920-1550-1.aspxHi All,Good morning,I have requirement export data from SQL server to CSV using SSMS, because we don't have access to execute SSIS package from management studio using [b]XP_cmdshell [/b], so can you please any one help me ? how to export the data without xp_cmdshell ?Thanks in Advance.Kanagarajan s.Sat, 15 Jun 2013 19:57:04 GMTmanavairajanhttp://www.sqlservercentral.com/Forums/Topic1463821-1550-1.aspxHi,Backup jobs failed frequently due to as below error.. after resarting server backup jobs running fine..Pl. suggestion me if any pacth or CU are need to apply..Current verion- SQL2K8 32 bitService pack - SP3Error messages[code="other"]Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:10:00 AM Error: 2013-06-15 00:10:01.31 Code: 0xC0010018 Source: Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"> <DTS:Property DTS:Name="DelayValidation">0</DTS:Property> <DTS:Property DTS:Name="ObjectName">Local server connection</DTS:Property> <DTS:Property DTS:Name="DTSID">{11A126D7-9C4A-417A-9" from node "DTS:ConnectionManager". End Error Could not load package "Maintenance Plans\DB_Backup" because of error 0xC0010014. Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors. Source: Started: 12:10:00 AM Finished: 12:10:01 AM Elapsed: 0.421 seconds. The package could not be loaded. The step failed.[/code]Fri, 14 Jun 2013 23:43:42 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1463279-1550-1.aspxSuppose i have to replicate a table from production to reporting server. Production is publisher and report server is subscriber. It is also required that table data may be updated at subscriber. However updates on subscriber should not be send back to publisher. Is it possible to configure such a setting for replication?ThanksThu, 13 Jun 2013 13:23:36 GMTAShehzadhttp://www.sqlservercentral.com/Forums/Topic1028664-1550-1.aspxI have been running into some weird problem - I have SSIS jobs scheduled to run on a regular basis. Most of the time they run without any problem, but lately I got more and more sporadic job failures like the following message. The failure does not persist for long as the same job will run fine later without any intervention.Everything else on the server seems fine. I don't see any info in the sql error log. Any ideas? Thanks!------------Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:23:26 AM Could not load package "xxxxxxx" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed. Source: Started: 9:23:26 AM Finished: 9:23:42 AM Elapsed: 15.039 seconds. The package could not be loaded. The step failed.Wed, 01 Dec 2010 07:48:21 GMTMichelle-138172http://www.sqlservercentral.com/Forums/Topic1463643-1550-1.aspxHi Experts,I know the permission required to create a DB snapshot is same as creating a database. Can anyone please let me know how to give minimum permission for creating the same other than dbcreator.Thanks in AdvanceFri, 14 Jun 2013 09:24:29 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1463331-1550-1.aspxTo check for recent failures of maintenance jobs, a person can run a query such as -- Identify SQL Agent jobs (and maintenance plan subplans) that have failed in the last few days.-- If the Agent is not running, jobs will not start and this query will not find any problems. use msdb go select convert(char(13),server ) as Server , convert(char(60),name ) as JobName , convert(char( 7),step_id ) as StepNum , convert(char(35),step_name) as StepName , dbo.agent_datetime(run_date, run_time) as RunDateTimefrom sysjobhistory, sysjobswhere sysjobhistory.job_id = sysjobs.job_id and -- join tables step_id <> 0 and -- do not select job overview, only the details run_status <> 1 and -- select failed jobs datediff -- last few days ( day, dbo.agent_datetime(run_date, run_time), getdate() ) <= 8order by Server, JobName, StepNum, StepName, RunDateTime desc -- most recent jobs firstThat's all very nice but does not help much if the Agent is down and jobs don't even start running. Is it possible to run a T-SQL query to determine if the agent service is running? If you've got a lot of database servers to check, it would sure be convenient to run a query on all of them to check agent jobs and another query to make sure the agent service is up.Thu, 13 Jun 2013 15:43:29 GMTaiki4ever-796329http://www.sqlservercentral.com/Forums/Topic1462285-1550-1.aspxHi All,2days ago we have a dead lock.how to find out, for what cause the dead lock got occured?Tue, 11 Jun 2013 11:21:32 GMTAAKRhttp://www.sqlservercentral.com/Forums/Topic1459212-1550-1.aspxHi Guys,Need some information on Index Rebuilding. (sort_in_tempdb = off)When performing index rebulding (for indexes > 30% fragmented), will we expect to see an increase in the user transaction log size (LDF)? Or we will expect to see an increase in the database size instead (MDF)?Anyway to calculate how much size needed for rebuilding indexes?Let's say if i need to rebuild 3 indexes, each about 5 GB.After rebuilding the first index, will the temporary space used for rebuilding the first index be reuse for the 2nd index? Or we first need to perform a transaction log backup before the space can be reused?thanks!Mon, 03 Jun 2013 06:07:33 GMTchewychewyhttp://www.sqlservercentral.com/Forums/Topic1462879-1550-1.aspxI have had times when I would like to get the Query Plan from the Plan Cache.I have Query 1 below (MSDN) which give me the query text and last execution times but not the Query Plan.Or Query 2 below (also from MSDN) that give me the Query Plans but not the Last_execution times or other info like READ Write stats etc etcI am having to run a separate query to get this [SELECT * FROM sys.dm_exec_query_plan(plan_handle) ] using the returned Plan Handle.Is their any way to get everything in the one query? I have tried to cross apply sys.dm_exec_query_stats to Query 2 to give me the execution times but that was a guess and not right.thanks[b]Query 1[/b]SELECT dbid, sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes, plan_handleFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.last_execution_time DESC[b]Query 2[/b]SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan,*FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)Wed, 12 Jun 2013 19:29:42 GMTUncleBorishttp://www.sqlservercentral.com/Forums/Topic1463250-1550-1.aspxI have several tables in a database that shows a high percentage in fragmentation. I created a maintenance plan and runs succesfully however, the fragmentation is not changing. For example the table below has a 52% fragmentation and pagecount of 109,208 but the rebuild index task doesn't change it.objectname objectid indexid partitionnum frag pagecountpricevalue 832955731 1 1 52.0331600907694 109288I'm just wondering if there's anything I need to do or that I'm doing wrong.Thank you.Thu, 13 Jun 2013 12:24:06 GMTHildaJhttp://www.sqlservercentral.com/Forums/Topic1462702-1550-1.aspxI'm getting the following error when running dbcc on a database server containing three different databases. Two of the commands run fine execpt for this database. What is the best way to handle this? I read different suggestions and one of them is to restored from a backup file, would that be a good solution?Executing query "USE [TestDB] ".: 50% complete End Progress Error: 2013-06-12 00:01:09.92 Code: 0xC002F210 Source: Check Database Integrity Task Execute SQL Task Description: Executing the query "DBCC CHECKDB(N'TestDB) WITH NO_INFOMSGS " failed with the following error: "The In-row data RSVD page count for object "table1", index ID 1, partition ID 306297224822784, alloc unit ID 306297224822784 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. The In-row data RSVD page count for object "table2", index ID 1, partition ID 369221408063488, alloc unit ID 369221408063488 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'table1' (object ID 378757448). CHECKDB found 0 allocation errors and 1 consistency errors in table 'table2' (object ID 1338904287). CHECKDB found 0 allocation errors and 2 consistency errors in database 'TestDB'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Progress: 2013-06-12 00:01:09.94 Source: Check Database Integrity Task Wed, 12 Jun 2013 09:39:39 GMTHildaJhttp://www.sqlservercentral.com/Forums/Topic1167814-1550-1.aspxI've recently installed SQL Server 2008R2 with SP2 and SSIS. When we try import SSIS packages from SQL 2005 we get the following error: "Object name "䎹녞숋㤧 " is not valid. Name must contain only alphanumeric characters or underscores "_".Nothing unusual about the install, collation of SQL is Latin1_General_CI_AS.Package is going to the File System, Package name is Package1 and protection level is "Keep protection level of the original package"Has anyone seen anything like this. Any help would be appreciated.CheersLeoTue, 30 Aug 2011 14:37:07 GMTLeo.Millerhttp://www.sqlservercentral.com/Forums/Topic1462966-1550-1.aspxHi ,The fullbackup job failed with the below error :MessageExecuted as user: NT AUTHORITY \ SYSTEM. Allowed for a file or file group "XXXXXXX_event" for backup, because it is not online. You can use FILEGROUP or FILE clauses execute BACKUP, to limit the options include only online data. [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failedI chcked the fulltext catalog file is on online.please let me know how to resolve this issue ..Thanks,Thu, 13 Jun 2013 04:18:24 GMTLavanyasrihttp://www.sqlservercentral.com/Forums/Topic1462451-1550-1.aspxHi,I need a help. am doing shirnk on 160gb( but data is just 30gb) to claim space. This db has Varbinary columns. Shrinkfile for 1GB (Dbcc shrinkfile ('Filename', 159) takes 35 minutes and i had to kill it since it blocked other session.have you anyone faced this problem when shrinking varbinary data? Do you have any better idea to reclaim space.? thanksTue, 11 Jun 2013 23:08:08 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1462796-1550-1.aspxHi,I wanted to start a short discussion about Lock Pages in Memory (LPIM) and any possible impact (positive one) on disk's latency.Recently, I contacted MS to get some help on a Cluster's issue. Long history short, the case is still open but it looks it has been resolved. However, they did recommend the use of LPIM on my SQL box.Now, I am running a standard version of SQL 2012 on a two node Cluster. The Cluster does not lack of RAM (96GB per node, with 64GB assigned to SQL, dedicated box) But just today and after a few days of that change, I noticed on my RedGate Monitoring tool, that read and writes (latency) on my Data LUN went down to zero! Could be that such action put most of my data into RAM, improving latency in such a dramatic way? Or ... should I look for any RedGate patch / issue? It's just hard to believe that a simple change, made such a huge improvement.I know how LPIM works, and that LPIM will avoid the Os to trim any memory from SQL server, etc, but I find these results really outstanding and dramatic, especially on a dedicated box.Wed, 12 Jun 2013 13:21:28 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1462366-1550-1.aspxI am working on a sql database which has multiple schemas. Lets say schema 'XXX' has 50 tables and i have 3 set of globalgroup users accessing this schema. Can i restrict the table select/delete/insert/update at global group level? For instance users belonging to group A should be able to create/modify/delete/insert /update table or records. Group B can only select data from all tables and groupc can select only 10out of 50 tables.is this doable? If so a user with db_owner privileges can set these up? Any input or ideas are greatly appreciated. TIATue, 11 Jun 2013 13:55:31 GMTishaan99http://www.sqlservercentral.com/Forums/Topic1462586-1550-1.aspxHi Experts,I need to copy backup files of around 500GB from one location to another,need to check the existing file date and available disk space then only i need to copy the files. Can anyone please help?Thanks in AdvanceWed, 12 Jun 2013 06:28:47 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1462585-1550-1.aspxFrom time to time our front end application gets 'unable to connect to server: abc' error. The error just goes away on its own. We tried to increase max worker thread, which did not help. Could someone point me to the right direction? FYI, we are on sql 2008 R2 standard.Wed, 12 Jun 2013 06:24:20 GMTKATHLEEN Y ZHANG