We have been asked whether it is possible to completely delete sensitive data. When data is deleted it is not actually completely removed from the database but is marked as deleted. It is possible to zero out that data using sp_clean_db_free_space but this doesn't affect the transaction log.
We had hoped that CHECKPOINT would clear the data from the log but we are not completely certain of this. So the question becomes is there a built-in command or function in SQL Server 2008, or subsequent versions, that will completely remove deleted data, both from the transaction log and the database itself? Or is there a 3rd party tool to accomplish the same thing?
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.
I have a table in sql server 2000 which has over 94000 records. I have to delete a record from table ,which record having a language other than english . I need to clean the table by removing all the data which are in other language . My main table has 12 fields .
Hello, I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager.
There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database.
We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment?
Dear netters,We are looking to build a SQL Server database that will be hooked up toa Web server (WebLogic or a .NET).The database will house data for multiple customers, and therequirement is to have no customer see other customer data. Web serverwill be responsible for authenticating users (ids and passwords will bemaintained there/possibly stored in the database, but will not beactual database logins) -- and establishing pooled connections to thedatabase, using some sort of a proxy login.Ideally, we do not want to have the database itself visible to "theworld", it should reside behind a firewall, and be accessible throughthe Web server, which will do all authentication and routing.We want to have either tables or views defined in such a way that:1. Users can only see their own data and NEVER can see anybody else's.2. Users' access to the database is logged (this includes updates aswell as queries).3. We would like to provide some sort of a reporting mechanism -- whereusers can form their own queries. So, something like a Crystal or anAccess front end, that users are typically familiar with... The problemis that this requires users having direct access to the database (?),and opens up tables (unless, there is a way to create parameterizedviews that will always restrict access to a subset of records).Any advice on how to best implement this is greatly appreciated.Thank you in advance!
I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?
We are running 2014 enterprise. Have there been any features added to sql server that ease the challenge of restoring only data? I often find myself creating separate databases so that a restore of a db's data doesnt also restore procs and functions and vice versa.
Server consolidation is the word of the day here. One of the elements is to use, when applicable, virtual machines in our devstageprod environment. Our sql servers (with local storage) are well utilized and run fine. I don't want to move the entire server (sql 2005 engine + data) into a VM as I think performance will be bad. But, per my question, if only the sql engine is running in a VM while the data is stored on our SAN, I think we may achieve acceptable performance.
Do you have any thought or experience with this mix of Vm and SAN from a sql perspective?
I have downloaded he trial Edition of SQL2005 Enterprise Edition. The data base engine does not install. Since I have previously installed Visual Studio 2008 which has VS 2005 components I uninstalled the SQL 2005 (which only has the configuration tool.
I also uninstalled he SQL Express and Compact Editions. When I tried to install SQL 2005 Enterprise once again the Data Base Engine did not nstall. I do get a warning that IIS is running in 32 bit mode in the pre-setup list,(I am running W2K3 R2 x64). I do not know what tro do I am downloading the trial software again. I need to have the data base engine and management studio. I am baffled as to why it doesn't install the Dtat Base Engine. In my add and remove programs there is no instance of SQL installed. Can anyone help? Thank you in advance. Joe
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.
We are currently setting up out production server to the following requirements:
1. Every month, delete records that haven't been changed in the last 90 days. 2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.
The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.
Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
Ok, so I have an issue, was wondering if anybody else has any suggestions.
I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.
1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text) 2. Table contains about 70 million records 3. Table has 6 indexes associated to it 4. Table has 2 views associated to it. 5. Table has 8 foreign keys associated to it.
I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.
When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.
The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.
This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)
Any suggestions on how to delete records efficiently?
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.
I looking for a way of measuring how many MB of outbound SQL data is being transferred during the course of a day from an OLTP instance. Data is pulled from this server by a DW in the same domain and adhoc via a linked server. I'm looking at perfmon but nit sure if this is the correct tool and if so which counters to use. I'm looking for the total MB/GB over the period not the MB per sec.
I have 5 TB of data in production and my development team requested to restore only last month data(<=500 MB ) in staging server and last 2 months data in pre production.
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
What are the optimal values for this parameters? How it depends from queries characteristics?I create an application that insert some data in database. It'll work on different servers with different load and performance. I want to prevent timeout exceptions.
