Tag Info

Hot answers tagged

3

Is there a particular reason you are specifying TRUNCATEONLY? As per the documentation on DBCC SHRINKFILE: TRUNCATEONLY Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if ...


2

DECLARE @d DATE = DATEADD(DAY, -5, CURRENT_TIMESTAMP); SELECT keyid = 12345, [date] = d, Hits = COUNT(*), HomePageHits = COUNT(CASE WHEN Action = 'Home' THEN 1 END) FROM dbo.mytable WHERE [date] >= @d AND [date] < DATEADD(DAY, 1, @d) AND keyid = 12345; (I'm assuming where column='Home' was a typo.) Some tips: Bad habits to kick : ...


1

Firstly, you are correct in that the (slightly counter-intuitive) way to prevent access to the [sys] and [INFORMATION_SCHEMA] schemas is to first ensure that the login (well, server-level principal) exists as a user (erm, database-level principal) in the master database. Assume you have a SQL login for simplicity: CREATE LOGIN [testy] WITH ...


1

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time. DECLARE @FileMeta table (columns nvarchar(200)); INSERT INTO @FileMeta EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak'; SELECT * FROM @FileMeta WHERE columns not like '%volume%' AND columns is not ...


1

You could consider raising a connect item (Microsoft bug report) as you have a reliable "repro", but then there are a number of workarounds for the problem - eg there is no real need for the APPLY, eg ;WITH TableA AS ( SELECT 101 as A_ID ), TableB AS (SELECT 1 as B_ID, 101 as B_A_ID , 'xxx' as B_Courses UNION ALL SELECT 2 , 101 , 'YYY' ...


1

There are a number of them, a lot of articles on the topic if you have google at it. Some of them: Default Schema for Windows Groups User Defined Server Roles Enhancments to Auditing User Contained Databases, authentication without logins. TDE - Transparent Data Encryption Hashing Functions - improved.


1

Since you have downtime, best is to use this powershell script- with backup restore option This script will take care of moving databases, logins, jobs,etc on the new server. make sure to use backup restore switch as opposed to detach/attach.


1

There is no built-in way to backup all the databases into a single file, and even if there was, that file would probably become impractically big. I would go with the ideas you got from your original question, and implement those in an automated script that loops through all your databases; One script to back every database up, one script to restore those ...


1

I thought that ALTER AUTHORIZATION might offer a way to transfer ownership, but no, according to the docs (emphasis mine): Ownership of the following entities cannot be transferred: <many things> and event notifications. So, script them, drop them, and re-create them under a more generic account that is not tied to an employee. This is similar ...



Only top voted, non community-wiki answers of a minimum length are eligible