Tag Info

New answers tagged

0

Perhaps this is a good start - You'd have to calculate: each data type times expected row count. Of course this is approximate result due to format/and possibly padding overhead, but might be a start.


2

Right off the bat, your script completely ignores schema, so it will fail unless all of your production servers only use dbo explicitly as the namespace for all tables (and nobody has a default schema other than dbo). You're also using a deprecated data type (NTEXT) for seemingly no reason. Also I don't think the WHERE clause against sys.tables is necessary ...


0

I did notice a couple of things. I highly recommend using more than one character variable & temp table names. It makes it harder to read and next year when you are reading this again (or someone else is) you won't remember what they mean. It's not that hard to use @DBCounter instead of @I for example. Your DB list query at the top will create ...


0

Here's a powershell solution: import-module sqlps; $s = new-object microsoft.sqlserver.management.smo.server '.' foreach ($db in $s.Databases | where {$_.IsAccessible -eq $true}) { $u = $db.users | where {$_.Login -eq 'foobar'} if ($u -ne $null) { #login is mapped to a user in the db foreach ($role in $db.Roles) { if ...


0

Based on my understanding, this is just a noise that you can ignore. This event is a part of sqlserver.security_error_ring_buffer_recorded event. It is detailed logging telling you what it is doing with the security errors (e.g. failed logins) to make sure they don't get lost. The API ImpersonateSecurityContext (provided by SSPI) allows a server to ...


3

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb. This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to. ...


3

This script is slightly modified from a script mentioned at will do what you are looking for. Replace 'ThursdayClass' with the login you need info for. https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ SET NOCOUNT ON CREATE TABLE #temp ( SERVER_name SYSNAME NULL , Database_name SYSNAME NULL , ...


1

Try sp_dbpermissions. It will probably give you more info than you need but it will do what you want. Once it's installed run this. sp_dbpermissions @dbname = 'All', @LoginName = 'LoginName' Fair warning at the moment it does a "like" match so if other logins are similar and match then you will see them also. For example MyLogin and MyLoginForThis will ...


2

Sadly you are going to have to iterate through all of the databases in order to get the information. You'll want to join sys.database_principals to sys.server_principals for each database matching on the SID. Don't use sp_msforeachdb as it is known to miss databases at times. Self plug, I did something in PS for auditing permissions that might be a ...


1

Assuming the row in the config table is identified by WHERE DB = @DB: UPDATE msdb.dbo.db_restconfig SET FilGroups_Move_To_FullQuery = STUFF((SELECT ',MOVE N''' + LogicalName + '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1) + @path_name + REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\', REVERSE(physicalname), 1) - ...


3

Yes, as ypercube already said. The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.


3

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation. Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored ...


1

I believe that in sql server 2005 you can create a stored procedure that references a table that does not exist. I have just done it, see my example below. --set parseonly on select @@version --Microsoft SQL Server 2005 - 9.00.5000.00 (X64) --Dec 10 2010 10:38:40 --Copyright (c) 1988-2005 Microsoft Corporation --Enterprise Edition (64-bit) on Windows NT ...


1

It Requires ALTER TRACE permission on the server. Note :- This feature will be removed in a future version of Microsoft SQL Server (2014 above). Avoid using this feature in new development work.


1

The easiest way to setup an alert is to base it off the error number that occurs for failed backups. That would be error 3041. To setup the Alert USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Failed Backup Alert', @message_id=3041, @severity=0, @enabled=1, @delay_between_responses=0, ...


0

You have to tell it what the 'A' is... AND B.EFFDATE = (SELECT MAX(EFFDATE) FROM OSIBACKUP..OSIBANK.ACCTMIACCTHIST Z, OSIBACKUP..OSIBANK.ACCT A WHERE Z.ACCTNBR = A.ACCTNBR AND Z.EFFDATE <= @ReportDay)


0

I was able to get guidance from the following MSDN link: Configure Windows Service Accounts and Permissions The short version for the solution is that either Read, Execute or Full Control are needed on the leaf-level subdirs under the system root (DATA, LOG, FTDATA, etc). On my host, NTFS permissions weren't propagating from the mountpoint volume root or ...


1

Note: This is for Microsoft SQL Server. For testing purpose, I downloaded a corrupt database backup file from SQLSkills.com Below is a quick script that will help you (obviously you can adapt the logic to do it dynamically for all your databases and send email in a nice HTML format) : begin try BACKUP DATABASE broken TO ...


3

Aaron has answered your question perfectly. I am just adding the full script that I wrote to test in my environment. Below is the script : /************************************************************************************ Author : Kin Shah Purpose : Written for dba.stackexchange.com http://dba.stackexchange.com/q/81505/8783 ...


3

As with the linked answer, you should be able to do this by using TRY/CATCH; the catch (no pun intended) with the other answer is that, because the name of the linked server is validated at compile time, in the event the test fails, the batch doesn't execute. So instead, you can put the linked server test inside dynamic SQL: DECLARE @srv SYSNAME = ...


4

I think you have things just a little messed up. How about: CREATE PROCEDURE dbo.usp_restore @backup_path NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON; RESTORE FILELISTONLY FROM DISK = @backup_path; END GO Creating the procedure should be completely separate from any individual invocation. So in a different window, or two weeks later, you should be ...


-3

I think you can only do this with dynamic SQL. Replace the body of your SP with the following: DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak' IF object_id('sp_restore') IS NOT NULL drop procedure sp_restore go CREATE PROCEDURE sp_restore AS BEGIN DECLARE @SQL NVARCHAR(MAX) SQL = 'RESTORE FILELISTONLY FROM DISK = ...


5

I have to admit I expected this to require a lock on the database which would mean you couldn't do it while someone else was in the database. But just to be certain I did some testing and was able to update the setting while I had an open transaction on a table in the database. So technically yes you could make the change during the day. However, just ...


1

I'd err on the side of caution and do it at the end of the day. May not make a difference but anytime I switch settings like that, I just wait for the evening when i have my maintenance window. Good for you for turning it off!


1

I would wait until the end of the day and then turn off auto shrink and never turn it back on. If you are low on disk space you will need to look into to getting some more and then turning auto shrink off. The reason for this is it creates a lot of fragmentation. See this to see how bad this can be.


0

This is a network timeout issue. You can troubleshoot this with your network team or by running a network packet trace that is filtered to SMB traffic between the SQL Server and the destination file server. A word of caution, this trace will be intensive with the tools that I am recommending and I have had mixed success with them (I last used it in an ...


2

From the xp_logevent topic in the documentation: ' message ' Is a character string with a maximum of 2048 characters. So, I would change this: Declare @message nvarchar(max); To this: Declare @message nvarchar(2048); Or possibly even non-Unicode (the docs aren't clear): Declare @message varchar(2048); Also, severity should be delimited as a ...


3

First I'm assuming since you didn't specify that you want your primary key to be clustered. It's the default and most common usage. If not then the order of your column will depend more on the selectivity of the two columns and your usage. However, if you are making your primary key clustered then if you look in this Technet article on the aligning ...


0

I've seen the query planner so similar things with index-less temporary tables, even sometimes when the number of rows in the temporary table is small. Try adding an index that covers the columns you are joining and filtering on, to set if the planner uses the stats from these to notice it can achieve the goal a more efficient way. Additionally: if you do ...


4

While this looks like more code, I think it's more straightforward because it mimics what is asked in English more closely (find the tables without a clustered index, not the tables with an index_id of 0) it doesn't need to apply distinct it avoids pesky metadata functions and awkward functions like objectproperty/ex it includes the schema SELECT [table] ...


0

Maybe this is not an answer to your question but I believe it is relevant. I would write a comment if I could but I want to add the pictures and the script I used. I had the same problem while ago, I was getting the following error message while trying to re-attach a database: The way I sorted this out was impersonating the login used to detach the ...


-3

if you have identity field, PK should be on 'K1' and 'id_filed' create table T (...) on partitionscheme(K1) alter table T add primary key clustred (K1,id_field) on partitionscheme(K1) if not alter table T add primary key clustred (K1) on partitionscheme(K1) ,but the clustered index always must be on same file group as table.


1

Step 1: Get query session id using below query SELECT sqltext.TEXT, req.session_id,----- SPID req.status, req.command, req.cpu_time, req.total_elapsed_time,req.start_time,db_name(database_id) DatabaseName FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where [status] <> 'background' and command<> 'TASK ...


1

Use KILL (SPID) to kill the query.


5

Add brackets with table, as Group is a reserved word for Group by clause in SQL Server. SELECT GroupID, GroupName FROM [dbo].[Group]


2

Query sys.indexes using: select distinct [table] = object_name(object_id) from sys.indexes where index_id=0 and objectproperty(object_id, 'IsuserTable') = 1 or: SELECT SCHEMA_NAME([schema_id]), name FROM sys.tables WHERE OBJECTPROPERTYEX([object_id], 'TableHasClustIndex') = 0; If you are looking for a complete index analysis, you should take a look ...


1

Well based on Reason:15105 I think that SQL Server doesn't have permission to access to the path you check the permissions of the account that is assigned to the SQL service account? or other reason would be that there is not enough free space to create backup.


4

Code gets backup history for all databases on a server and generates HTML report Step 1: DECLARE @Body VARCHAR(MAX), @TableHead VARCHAR(MAX), @TableTail VARCHAR(MAX) SET NoCount ON ; SET @TableTail = '</body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: ...


2

Try this SELECT p.ProductID, p.ProductName, PS.CurrentStock FROM Product P JOIN Productstatus PS ON PS.productid=p.productid WHERE PS.LocationID = 1 AND PS.StatusDateTime = '2014-08-27 23:59:04.910';


0

try this to update using a sequence... You have to do the TOP because of the order by clause in the update statement. I used this statement on SQL SERVER 2012 update invoice set RecNo = (next value for seq_invoice_recno) where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null order by invoiceId)


2

How can it automatically change host of one if the other one was dropped ? From the client application side, your application connection string should use FailoverPartner pointing to secondary or mirrored server. If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the ...


1

Yes, there is a configuration for how many errorlogs to maintain. It can be found in the registry key at: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs You may need to add this key.


10

There is a setting if you right click on "SQL Server Logs" and select "Configure". You can tell it how many log files you want to keep. Once it reaches that number it will start removing the old ones.


7

SQL Server recycles error logs automatically, as long as you configure it correctly. See http://msdn.microsoft.com/en-us/library/ms177285.aspx . I've copied the salient points below: In Object Explorer, expand the instance of SQL Server, expand Management, right-click SQL Server Logs, and then click Configure. In the Configure SQL Server Error Logs ...


6

Restart SQL Server 7 times. You will see you still have only 7 ERRORLOG* files (depending on version). This is the current log file and the 6 most recent log files. Note: You may need to change 7 to something else, in the event you changed the number of error logs SQL Server keeps. The max, I believe, is 99. But no matter what your current setting is, it ...


7

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation: If the query has no ORDER BY clause, the order of the rows is arbitrary. In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too: WITH TIES Specifies that additional rows be returned from the ...


1

An alternative, is if you are processing a higher volume of records, you may actually be getting a misleading error. It could be that you have used up all of your ports on your PC, you can check this by running Sysinternals TCPView. If you see thousands of TIME_WAIT, this is a possible cause. In essence, after your application closes its connection to the ...


3

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects. By default, SqlConnection has a max pool size of 100. The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable ...


2

Automatic failover requires a witness. See Database Mirroring Witness: To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to ...


1

I found a script: SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName ,dosi.virtual_machine_type_desc ,Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi If you have a CMS configured, run the below Script from your CMS against multiple servers: SELECT ...



Top 50 recent answers are included