SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Zombie Data

Most of us that work with data are concerned about losing any of the bits or bytes we are responsible for. Many of us practice restores when we can, we complain about the low disaster recovery budgets we're allowed, and we regularly check our systems for corruption. Even those of us that aren't extremely diligent in our daily data protection are often worried about losing our jobs if a disaster causes data loss. Many of us don't even trust our users, preferring to implement logical deletes in applications rather than physically running a DELETE statement.

However there are times that we do want to remove data from our systems. We may find data quality is poor and want to erase the results of an ETL load. We may find ourselves bound by regulations that require the removal of data from our systems. We may upgrade old software and end up with copies of obsolete databases whose contents have been copied and reformatted by a new version of an application.

This article talks about the data-pocolypse, and in somewhat of a jesting way, but it has a few good points that we may want to consider when we do need to remove data permanently. We should understand that deletes are not always deletes, and if a permanent solution is needed, we should use a utility to wipe the drive or physically destroy the hardware. However there are other places we should worry about old copies of data. Backups should be deleted from remote storage that might not have cleanup jobs running anymore if the servers are decommissioned. We should be wary about taking databases offline, or detaching them without physically removing the files. Development machines, laptops, etc. should have data removed if we are sure we don't need it again.

We should be especially careful about security access to servers that we are not using. Users might easily have links or pointers to old servers, and mistakenly connect. Make sure you remove access when you decommission the instance. Be careful about keeping old file exports, such as reports or feeds that were generated in the past. It might not be practical to wipe backup tapes, but be sure you've changed documentation and surfaced the information to all administrators that copes of databases restored from tapes after xxx date are not useable in DR situations.

Of course the first thing you should do is make sure you have a good backup of the most current version of your data before you start deleting older copies. Delete this last, after you're absolutely sure that users are no longer going to request a restore. I wouldn't even ask users about this, however, because they may not be sure themselves. Instead I'd set a reminder a few months in the future to go back and delete this one, last, most current backup.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.3MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
John says Locked out again! I can't believe I forgot my keys, that's the second time this week.
No John, it isn't that kind of lock. SQL Monitor
What you're experiencing is a DEADLOCK. It's okay though, there is hope. Kalen Delaney has a free eBook…
John says A DEADlock! I'm dying!?
Now don't get too wound up by the name, there's nothing to worry about. Lightbulb says
SQL Monitor I knew I should have reduced my salt intake.
I think you're getting distracted. Let's start again. SQL Monitor

Don't. Panic.
Download SQL Server Concurrency: Locking, blocking and row versioning free.

Featured Contents

 

Database User Superfluousness In SQL Server

Thomas Knight from SQLServerCentral.com

In this article, Thomas Knight analyzes and critiques the design of, and experience with, the "dual-level" user security in SQL Server. He then demonstrates a better theoretical alternative. More »


 

Disaster Recovery and the Big Data Application

Additional Articles from Database Journal

Ask database administrators how they implement disaster recovery in their big data environments and you'll get two typical responses: DR plans are not necessary and backups take up a lot of space. Despite this reasoning, a disaster recovery plan for your big data implementation may be essential for your company's future. More »


 

From the SQLServerCentral Blogs - Basic RegEx in SSMS

ArthurOlcot from SQLServerCentral Blogs

Have you ever had that moment where you have gone "Geez.. I wish I'd learnt this stuff ages ago" after... More »


 

From the SQLServerCentral Blogs - Inside the Statistics Histogram & Density Vector

Klaus Aschenbrenner from SQLServerCentral Blogs

You know the problem: the “Estimated Number of Rows” in an operator in the Execution Plan is 42, but you... More »

Question of the Day

Today's Question (by Steven Neumersky):

A week of Analysis Services

You have deployed a cube successfully, but your users tell you "it does not make sense to roll up a certain attribute hierarchy in the currency dimension to "ALL". What property must you change in order to disable that attribute's "ALL" level while keeping the rest of its attribute hierarchy intact?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Hierarchies.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Professional SQL Server 2012 Internals and Troubleshooting

The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steven Neumersky):

A week of Analysis Services

SQL Server Analysis Services (SSAS) loves memory. From time to time you may need control the amount of memory consumed by SSAS especially if you have other SQL Server components on the same server.

What structures WITHIN SSAS have "shrinkable" memory structures? Shrinkable is defined by SSAS as "structures that gradually build up to increase performance". (Choose 3)

Answer:

  • Calculated Cells Used Frequently
  • Subcubes in the storage engine cache
  • Dimension data

Explanation:

http://dennyglee.com/2012/06/28/microsoft-sql-server-analysis-services-multidimensional-performance-and-operations-guide/

(pages 112-113)

Non-shrinkable memory includes all structures required to keep the server running:  active user sessions, working memory of running queries, server object metadata, and the msmdsvr.exe process itself. Memory can still, however, be paged out by the operating system in certain cases.

Frequently used calculated cells use the calculation cache, a shrinkable structure.

Dimensions accessed by users are also kept in cache to speed up attribute and natural hierarchies.

Not all subcubes are cached, but those that are cached become part of the data/storage engine cache--which is a shrinkable structure.

(page 43)

The use of dimension or cell security disables global caching.

Some subcubes are not cached at the data/storage engine cache level but rather at the session cache level--not a shrinkable memory structure.


» Discuss this question and answer on the forums

Featured Script

Auto dump out database schema

Neil Bryan from SQLServerCentral.com

This is a PowerShell script that can be used to dump out a database schema to a text file (similar to the Generate Scripts option in SSMS).

I wrote it to dump out out live DB schemas which are not under source control (our dev schemas are).

We run the scripts once a week.

They are very useful when someone accidently overwrites say a stored procedure on the live server.

To run from the command line...

Powershell.exe -File "C:\PowerShell\Output_Schema.ps1" - $SQLInstance localhost\sqlexpress - $Output_Folder_Name "D:\Reports\Schema Dumps" - $Database Accounts_Database
In this example, the above script (Output_Schema.ps1) is placed in the folder c:\PowerShell the output text file is written to the folder D:\Reports\Schema Dumps and the database to use is Accounts_Database.
NOTE: The database parameter is optional, if omitted, then all databases on the server will documented.
A text file per database is generated.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Development - SQL Server 2014

Query performance - Some of developers have made modifications to database structure like indexes and later observed that the performance of queries have...

How check constaint works? - Hi All! I have added check constraint on table. I am calling one function in that check constraint. Actually I have to...


SQL Server 2012 : SQL 2012 - General

Failover cluster and global groups - I'm working through my first failover cluster install for SQL 2012, previously you had to put the service accounts into...

Best way to add constraint? - I'm adding a new column GenderID to a table Student. I am also adding a new table dbo.Gender with the...

Linked server - I define a connected server and got the following error: The thing name '[OXFORD\BA].master.dbo.Mirroring.Config' includes more than the maximum number of...

Searching all tables for a value, return table name - Hi guys, I am wanting to search all tables in my database for a value & then return the name of the...

Never Worked with SQL, but am interested in earning an MCSA in SQL Server 2012 - Hi, I have never worked with SQL before. I have been exposed to Access databases and can set up basic databases...

The current WSFC cluster quorum vote configuration is not recommended for this availability group - Hi, I'm setting up Availability groups using 2 nodes. I have Quorum disk configured. But when I'm using the wizard to create...

SSIS Foreach file enumerator - Hi, I want to practice SSIS. I am working on For each enumerator. I followed the link https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/ I created four text files...

How to Remove login limit in Windows server 2012 SP1 - hi All, I need help to remove login limit on one of my sandbox machine (SQL Server 2012 SP1). As of...

Sharepoint 2013 and SSRS 2012 licensing, once and for all - Lets say I have a Sharepoint 2013 farm with two servers, S1 and S2. S1 is a Sharepoint Web Front End...

auto-increment across multiple tables - Hi guys, I have multiple tables in a database, all of which have an ID column. I would like to auto-increment...

Always On: Backup on Secondary question - Hello all- I was hoping someone can shed some knowledge on Always On for me when it comes to back up...

permissions for stored proc that reads distributed partitioned view - I'm having trouble understanding the requirements for this. I'd like to have a stored proc that selects/updates a distributed partitioned view. I...

AlwaysOn sometimes becomes out of sync - Hi all, We have a AlwaysOn availability group that is serving a SCOM 2012 installation. We have just moved the VMs...

Help with Named Instance using Virtual Account to access filesystem - I decided during SQL 2012 install to use the new virtual account for the service accounts for SQL Server and...

Permissions to create and execute SSIS Packages? - We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. What is the correct amount...

xdg-open for open MS Word files - Hello friends, I have MS Word 2007 set up in it is own wine prefix for work documents. Sadly, my place...


SQL Server 2012 : SQL Server 2012 - T-SQL

Get list of all workers having same SET of Rates - Hi All, I have attached some test data for you that has two temp tables "#worker" and "#worker_rate". The issue...

Need to merge data from two tables - Hi All, Please help me in Merging table : --------Dummy TABLE create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int) insert into #Tbl1 (date1...

Exclude Weekends and Holidays - Hello, I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able...

Please help me with this year(date) totals comparing to column values in same table - Hello There Could you please help me here how to write condition for self table year records, such 2012 name and acctno...

How do I match values from a PIVOT? - I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events...

Query to get the CPU Usage,memory usage details of server - Can someone help me in providing a query for fetching the data for CPU Usage, Memory usage, blocking and all...

Querying a Supersession Two Column Table with Multiple Supersessions in both Columns - Hi Guys, I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server....


SQL Server 2008 : SQL Server 2008 - General

Database creation takes a long time...? - We're in the middle of creating a 2008 database with the following files: (see the attachment) The creation process has been...

SQL Job reports strange results - I've created the following script to report on the status on SQL logins: [code] EXEC msdb.dbo.sp_send_dbmail @recipients= N'XXXX', @subject = 'ED - SQL_Logins_Report', @profile_name = 'XXXX', @query = 'SELECT @@SERVERNAME SELECT...

Linked server error - I had setup linked server and when i query the it gives me below error. QUERY: SELECT top * from [Servername].[WH_SYSTEM].[dbo].T_ENCOUNTER_PHYSICIAN WHERE PhysicianID LIKE...

Processor Affinity - Why does this happen? - I've been battling away with a very badly designed application which is causing some issues on one of my SQL...

Getting extended properties from every database - Hello I'm trying to get all extended properties for each database in my server. In the test machine with ten databases the...

pivot data from header and detail with code from other table - I need to display data from both header and detail, details has to come from other table also --CREATE TABLE -----------------...

exec proce - hello all I Have this function: CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int...

Write a conditional query - Dear, I require to write a single query that if I pass a customer id, it will retrieve that customer's information....

Custom Error Message - Hi, BEGIN TRY CREATE TABLE [dbo].[TestTable] ( ID INT NOT NULL PRIMARY KEY ) RAISERROR('Everything is OK!', 5, 1) END TRY BEGIN CATCH RAISERROR('There was a problem!', 16,...

Change Tracking - Hi, I have Change tracking set on two databases, required for synchronization. The retention period is set to two days with auto-cleanup...

problem with openquery - hello all. I have this sp: ALTER PROCEDURE [dbo].[IcanSp_HumanResourceDailyMissionRegister] @EC INT AS BEGIN DECLARE @RoleID INT, @NationalCode NVARCHAR(15), @MissionEndDate NVARCHAR(20), @MissionStartDate NVARCHAR(20), @MissionConfirmDate NVARCHAR(10), @MissionNO INT, @MissionPlace NVARCHAR(50), @MissionCityID INT, @MissionDaysCount INT SELECT @RoleID

Memory Outside of the Buffer Pool - Hi All I understand that SQL Server's plan cache is memory taken from inside the buffer pool (Memory allocated by the...

SET DEADLOCK_PRIORITY option? - I am facing this error frequently.. during SP running under SQL Jobs at every day 3 times SP_name: exec.dbo.usp_ShiftdataReporting Transaction (Process ID...

Litespeed to restore native sql backups - Hi, I need to be able to restore some native compressed backups with litespeed. The reason being, we have very complex...

SSIS: Windows batch Script with SSIS variables to ftp receive files - hi Requirement: I need batch script to receive files from ftp to windows share drive .I need to pass ssis...

using count with select case - Hi, Table Name : Mark Sample data: [code="other"] select 20 as Mark union all select 30 as Mark union all select 70 as...

Failed to connect to server 2008. timeout expired - I am trying to login in sql server 2008 through sql server browser,for this i am using ip address of...

How to convert char 10/2013 to use the filter >= - Hello, I have a probllem with one part of my script which is: where BillingMonth = '10/2013' It is working fine now, but I...

Stored Proc And View Giving Different Results - I have been working on a project to establish how many people met certain conditions on any given day of...

Nested stored procedures and EXECUTE statements (Dynamic SQL) - Hi all, I have a wierd one. I can't provide specific code due to proprietary concerns, but let me see if...

Password checker automation - Hey all, Is anyone is familiar with any existing software out there that can be leveraged to automate checking of passwords? Historically...

XML Schema Collection and Dates - Not sure if this question belongs here or in the XML forums. I've been reading everywhere that SQL Server 2008 has...

Frequent Transactional Replication failures on SQL 2008 X64 - Hi Team, I am facing an issue with frequent Replication failures on SQL 2008 x64 --exact version --> Microsoft SQL Server...


SQL Server 2008 : T-SQL (SS2K8)

Is there a better query criteria? - Hello, I would like to know if there is a better way to do the following: [code="sql"]DECLARE @TempTab TABLE (PhoneNum VARCHAR(10)); INSERT INTO...

SP Help - Hi Guys, I want to know, is there a way I can BackUp all Store Procedure in .txt? I know...

Need to query db to return all instances for Canada. (Country, State, City) - I have the following will return Country, State, City I need to make sure that this query is ok. <code> SELECT ---DB_61318_itweb.dbo.itweb_Countries.CountryCode, DB_61318_itweb.dbo.itweb_Countries.Country...

Need to find returns where a given columns multiple returns do not contain a specific value in a different column of the result set. - Hello! Here's a simplified version of my table: Table Name - CompanyInfo Company Name Transaction Type HazCo A HazCo B HazCo C HazCo D ShimCo A ShimCo C ShimCo D BlintCo A BlintCo...


SQL Server 2008 : Working with Oracle

Execution plan difference when executed from Oracle VS SSMS - Hi. We are having performance problems on our server. We have found out that execution plans (attached) are very different when...


SQL Server 2008 : SQL Server Newbies

need to update 2nd table if data exists in table 1 - Hi everyone, I've 2 tables and both have a common field. What I'm after is to search table 1 and...

FINDING THE DATE WHICH IS NEAREST TO CURRENT DATE - i need to find the date from a column , which is less than and is nearest to the current date...

materialized view right course for a datamining query with 7 joins? - I have a query that has 7 joins and aggregates data and runs daily. It takes about 2 minutes to...


SQL Server 2008 : SQL Server 2008 High Availability

MULTI-Instance SQL 2008 R2 Cluster (same LUN can be shared between different Instance?) - SQL Server 2008 R2 Ent (On Windows 2008 R2 Ent) Hi guys, I am new in clustering. I would like to create a...

Convert Drive Letters to Mount Points in SQL Cluster - Hi Techies, I need some really urgent help help here. We use a single drive letter and use 4 mount points...


SQL Server 2008 : SQL Server 2008 Administration

Exception of type 'System.OutOfMemoryException' was thrown. ( I am need to see if there are DUPLICATE ROWS in table). - How else can I do it? How can I overcome this error? I am trying to see if there are...

CLR assembly DLL file location? Corrupted - CLR file location. Is it C:\windows\Framework\version? Frequently facing problems SSIS - backup package dll files are corrupted and maintenance plan not taken automatically...

Archiving Data - Hello Experts, we have a situation he a table grown to 180+ Gigs with several millions of records. this affects Re...

Moving Standby/Readonly DB to New server - Hi Experts, This could be a laymans query, please help. We are planning to move a StandBy/Readonly mode Database to a new...

Best all-round monitoring tool for - Morning all. I've been asked to garner the opinions of the masses as to a best-of-breed monitoring solution for our SQL2008...


SQL Server 2008 : SQL Server 2008 Performance Tuning

long durations for query intermittently - Hi Folks, I was running profiler on a sql2008 r2 standard instance 10.50.4290. Occasionally I get long duration on batchcompleted for...

SQL Server Profiler question (resource issue) - I keep having a long running profile shut down because the C Drive is full. I am saving the output...


SQL Server 2005 : Administering

Table permission denied - Hi all, I have given the full access(db_owner) to user for the test database. Users are able to work on Stored...

access Rights to Profiler - can i ristrict access right to run the Profiler specific to a Database. I need to give permission to user...

What recovery model should the replication subscriber db be using? - Three servers: A : production B : hot-standby C : replicated Database on Server A is in full recovery model. Transaction Logs are being shipped to Server...


SQL Server 2005 : Business Intelligence

Dyanamic Table Allocation in ssis - Hi friends, I have two database two db having master tables with same structure and data. Now i need to compare...

First/Last difference using MDX - Hi, I have a report that shows three lines for a group. The first line contains the first or initial values,...

how to deploy single ssis package in ssis 2012 . - Hi friends, in my project having 10 packages with shared connection used.here i need deploy only single ssis package please...


SQL Server 2005 : Development

SQL Query to find out MAX Qty and MAX Childitem of a parent item - Hi Folks, I need to write a query to find out all the parent items which has a MAX of Qty...


SQL Server 2005 : SQL Server 2005 General Discussion

Sell old SQL 2005 discs and CAL - Is it possible to sell on our no longer used SQL Server 2005 (standard edition) and 25 Client license? or is...


SQL Server 2005 : SS2K5 Replication

Trans replication with remote distributor, publisher upgrade question. - Hello there, I'm after a bit of advice. We have a 2K5SP4 publisher database with a 2K5SP4 remote distributor and over...


SQL Server 2005 : T-SQL (SS2K5)

Wut uhp wi dis? - On one of my development servers: Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise...


Reporting Services : Reporting Services

SSRS report with a "gridview" - Afternoon, I'm looking to create a report with a list of shops grouped by company area, the report would look something...

need expression for the field if there is no data - hello, how can I write an expression in the field which is a dollar amount value if there is a data...

Question on percentage in SSRS - I have a field with the data ($0 0.00% $125.67) in SSRS report how to write an expression in this field...

Diagram scale with dynamic 5 entries for 3 different values - Dear all I'm looking for a solution to configure a scala for a diagram which shows values from 3 different...

SSRS Expressions - Hi All, I have a field that contains some names and would want to lift the name part that I want...

SSRS Normal Quantile Plot - Is it possible to make a normal quantile or a normal probability plot in SSRS, where the y-axis is the...

Data Driven Subscriptions without SQL Enterprise - Introduction Do you have SQL Reports that need to go to different people but each person's report has different parameters? Don't...


Programming : Connecting

how to connect sql server 2008 over internet - hi everyone, can u help me how to connect sql server 2008 from my home to another place, another place...


Programming : General

View or function is not updatable because the modification affects multiple base tables - Hi There,   I am trying to do a csv import of some data into a view. The view outer joins two...


Data Warehousing : Integration Services

Data spilt in two tables - Hi All, I have flat file contains 20,000+ records. Column fields something like FName, Lastname, email, Phone, Address1, address2, sortocde, acoountno. I...

Deploy package to multiple servers - Hi, we currently have several servers running SSIS packages building tables for a data warehouse. These packages were created once and...

Implementation of regular expressions - I have a project where the schema is the report. IOW, its got Addr1, Addr2, Addr3, Phone1, Phone2...you get the idea. This...

Set SSIS variable = database connection manager name - Is it possible to set a SSIS string to only the name of a database connection manager or even the...


Data Warehousing : Analysis Services

SSAS Role Dynamic Security - Hi all, I have been reading this on Technet (Roles (SSAS Tabular): [url=http://technet.microsoft.com/en-us/library/hh213165.aspx]http://technet.microsoft.com/en-us/library/hh213165.aspx [/url] I would love to implement a cube some day soon,...

MDX Query filtering on two different dimensions using 'OR' operator - I need help with a simple MDX query. Suppose I have a measure group linked to a fact table called Flights,...

Cube not visible in Excel, PerformancePoint after SSIS processing - Good morning- I have an SSIS package that calls an Analysis Services DDL task to do a full process of our...

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...


SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...


SQLServerCentral.com : Articles Requested

T-SQL Comparing Rates - A writeup based on this thread. Show how this works and why one method may be better than the other [url]http://www.sqlservercentral.com/Forums/Topic1537283-3077-1.aspx[/url]

Spatial Indexing - A look at the different granularities of spatial indexing and why/where you've made decisions for particular queries.

Practical Extended Events - Looking for a writeup on a scenario where you have used extended events to solve some problem or improve performance....

Practical Spatial Storage - Looking for someone that stores spatial data in their database and can write a piece about how/why they store this...


SQL Server 7,2000 : Administration

Finding out Total Disk Space in TSQL - anyone know any workaround in TSQL who can give this result set? [b] Drive letter | Total Disk Space (MB) | Free Disk Space...


SQL Server 7,2000 : T-SQL

Linked server query error - I had setup linked server and when i query the it gives me below error. QUERY: SELECT top * from [Servername].[WH_SYSTEM].[dbo].T_ENCOUNTER_PHYSICIAN WHERE PhysicianID...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com