Added four new equally sized .mdf files to a production tempdb last night and restarted the instance. I was hoping to remove the initial file as it's not required and does not match the size+growth of the others. The option to 'remove' it is greyed out in the files tab of the GUI so I'm assuming that ALTER DATABASE [tempdb] REMOVE FILE [tempdev] will also fail.
Do I need to migrate any data in this particular .mdf before it can be dropped? Or would the simplest approach be to change the size of this file to match the others and drop tempdev3?
We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file V->Tempdblog having templog.ldf file
I have an instance with 4 datafiles for tempdb each set at initial size of 4G and growth rate of 100MB. After some time the initial file sizes seem to have changed automatically. They now read 3962,100,3688 and 2847 respectively. Is this something done by SQL Server itself? I cannot imagine that it was done manually.
I don't think there was a restart after the initial sizes of 4G were set, could this be related to the problem?
Error:- (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note: =>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error... => I executed above commands and restarted services...no change...
hello i make user(name was my_user) in sql2000 and also 2005 and access it in two datebase (mydb and tempdb) as db_owner after i restart my computer in tempdb access will just public and not db_owner and i cant create temp tables what should i do?
(i think make grant user and deny access may help but it dosent seems good)
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.
I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5. |OK| and (2) Add or Remove Programs Fatal Error during installation (after I clicked the |OK| button). Please help and tell me how I can solve this problem.
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks! - Mike
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.
I am having a hard time removing my SQL instance inside the Add/Remove program. After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it. There isn't any error message or whatsoever. Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box. Is there any way to remove the Sql Instance in my system?
I have been using a licensed copy of Visual Studio 2005 and MS SQLServer 2005 for some time but am only now trying out the Reporting Services functionality.
I have attempted to follow the instructions from url:
However despite confirming that the report services are running and also checking the configuration by following the information in the above web page I still get the following problems.
1. When attempting to create a project via the wizard I get the following error: Exception has been thrown by the target of an invocation. If an attempt is made to add a new data source I am unable to choose the data source type (eg: Microsoft SQL Server). I am not given a choice for the type, in fact the relevant drop down is blank.
2. A project can be created without the wizard but if I right click the Shared Data Source folder I do not see the next Pop up to set the data source parameters.
I assume I am missing something quite fundamental - however so far I cannot see what.
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \serverfolder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '---' is not yet available". can you guide me what are causes of this problem and how may i solve it?
I want get get results in sql that are all written in UPPERCASE but I want to receive them in Initial Case format I know UPPERCASE is UPPER lowercase is lower but what is Initial Case(first letter Capital in a word)
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.
Example data:
Jane,Smith Ron John,Dow L Mary Jane,Dow Welsh
The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:
-- I have a first name field that sometimes contains only the first name, -- sometimes contains the first name and the middle initial, and -- sometimes contains the first name, a space, followed by NMI (for no middle initial) -- how do I correctly grab the first letter of the middle initial in all cases? -- I have been playing with patindex but its harder than I thought. guess I need a case -- statement in addition to this. Any idea how I can do this? -- thanks!
I'm not a sql server savvy, so I need assistance on the following two scenarios:
A customer runs a script like this (slightly larger, but I ripped away the meat) --------------------------------------- create database test_database go
USE [test_database] exec sp_changedbowner 'sa'
use master; go sp_grantlogin 'server01CUSTOM_ADMIN'; go
use test_database; go
-- lots of table creations, where one of the tables are TEST_TABLE
sp_addrole 'ADMIN_ROLE'; Go sp_grantdbaccess @loginame = 'server01CUSTOM_ADMIN', @name_in_db = 'USER_ADMIN'; go sp_addrolemember @rolename ='ADMIN_ROLE' , @membername = 'USER_ADMIN'; Go
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[TEST_TABLE] TO [ADMIN_ROLE] GO ---------------------------
Now, if a person is added to the server01CUSTOM_ADMIN group, he/she should be able to do the following: (let's say it's a he)
- Create a test.udl-file (win xp). Set a provider to sql server. - On the connection-tab enter hostname of database server in the Data Source-field and use windows NT Integrated security. - When he now test the connection, it should work, since he has access to the database. - Also, using the dropdown "3. Enter the initial catalog to use:", he should see SOME datatables. Not ALL, not none. The ones that he has access to.
So, if TEST_DATABASE is the only access that server01CUSTOM_ADMIN has, that database and only that one should show, right?
In my customers scenario, some databases show (irrelevant ones), but not TEST_DATABASE. In my test, I still get ALL databases. Even after I rip the guy out of the Administrators-group and Users-group. He's only a member of the CUSTOM_ADMIN-group on server01. "Test connection" succeeds, and all the databases on the server shows.
What I hope for is following questions like "He's probably sysadmin, check it" etc, so that I can systematically (using your brains) filter out the reasons for these scenarios to happen.
Is there a way to decrease the initial size of a database/log file? I've noticed you can increase it, but if you decrease it, after you confirm the change and go checking again, you will see nothing happened.
Currently my db size is only 6 GB but the transaction log file initial size was set to 20 GB and has grown much way beyond the db size with the autogrowth feature turn on. The database was originally a test/development DB and was migrated to a production server including the log file. This probably caused the accumulation of transactions on the log.
We run backup everyday and tried to shrinkfile and file size did not change.
Can I change the "initial size" setting of the transaction log without causing any problems? Do I need to stop the service before I made the change assuming I made the change after the backup run? Or can I change it on the fly?
Via t-sql, how can I query for a file's initial size?
I want the same thing one finds by : Start SQL Server Management Studio, view, object explorer, right click [dbname], properties, files, Database files: Initial Size (MB).
Many thanks.
Anything will give up its secrets if you love it enough. -- George Washington Carver
I have a SQL Server 2005 Std. Ed. 64-bit installation. There is one instance supporting a single production database. I have a CLR udf. This udf uses the XMLDocument object to retrieve XML from a URL. When the CLR udf is executed, there seems to be an initial slow response time. Subsequent response times are very fast. If the CLR udf is not called for a few minutes and then called, the slowdown appears again.
Is there something happening behind the scenes with compilation or something like that which could cause this slowdown?
I am setting up 2005 Transacational Replication on a database that was created on SQL 2000. There are 1400 articles (tables, views, sp and functions). It takes 2.5 hours to create the snapshot. Then, once the distribution errors gets its first error, it keeps retrying and getting the same error. Q1: Can I tell it to record the error but keep going? Q2: How do I stop the distribution agent once it gets in the this state? I have been deleting the publication but that seems like overkill.
I am trying to figure out a more efficient way to identify all the articles that are going to get errors. Is there any way to test the articles to see which ones will get an error? My current process takes a long time just to identify one error (since I have to create the snapshot each time).
I connect, but when I try to access any table from this database I get an error indicating that the object doesn't exist and if I use the fullname xx.table I get no errors. What may be happening?
I would like to increase the initial size of a SQL 2005 DB from 150 to 250 GB to prevent automatic autogrowth; would this have any impact in production if you do it on the fly?
HI all... I am using MS SQL 2000 DB...from last 4 months....I want to take backup of the data from 1st january to 29th Feb 2004 and then trim this data to freeup some space in my dtabase. Is it possible?if yes hOW? Thanks in advance, Chandresh here
Hi, In a .net application there is a link that brings up a SSRS report. I have noticed that if it is the first time this report is requested i.e. Application has just been opened and the report button is clicked, then it takes a while to get this report to appear on the screen. But if this report is requested again (i.e. for the second time or more) then it only takes a few moments for the report to appear on the screen. So it seems that only the first time the report is requested it takes a longer time to get this report. Is there a way to reduce this initial load of the report? Thanks
A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.
A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.
The processing of the response message failed.
I've tried compressing the snapshot, and can see the bcp file in the snapshot.
