Modify File On User Database

Jan 20, 2008

Hello, I have gone to work for a new company and I have seen this company is running out of drive space on a daily basis. The DBA before me created huge databases. For example we have a database that it's created size is 103 GB. Only about 52 GB is ever needed. I know you can resize the tempdb but how about user databases? I have never tried this. Can this be done for a user database?

For tempdb
Start SQL from cmd line sqlserver -c -f -s%instance_name%
alter databse tempdb modify file

(name='tempdev',size=150)

View 3 Replies


ADVERTISEMENT

SQL Server 2008 :: ALTER Database To Modify Log File

Jun 25, 2015

We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1

Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The statement has been terminated."

Now tried to change the filegrowth of the log file to unlimited

ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);

The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB

View 6 Replies View Related

SQL Server 2014 :: Alter Database Modify Log File

Nov 6, 2015

I can run this command to make changes for 1 DB USE [master]

GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.

View 2 Replies View Related

How To Modify User Defined Datatypes

May 3, 2000

Hi,

I used user defined datatype but when I tried to change the length of the datatype I found out I could not modify the length by going to property of user defined datatype or I could not drop it without dropping the dependent tables. I got a problem. Anybody can help?


Thanks,

Wilson

View 1 Replies View Related

User That Can Alter And Modify Tables

Feb 7, 2008

What do I have to do in order to create a user that can alter and modify some/all tables?

Thank you.

View 2 Replies View Related

Modify Physical File Name

Oct 27, 2003

I know how to modify the logical file name: ALTER DATABASE SATutorial
MODIFY FILE (NAME = Tutorial, NEWNAME = SATutorial_data)
GO

How would I modify the physical file name from (e.g.) Tutorial.mdf to SATutorial_data.mdf?

TIA...

Al

View 3 Replies View Related

Is It Possible To Modify A Report By Using .rdl File?

Nov 7, 2007

Hello,

I need to modify an existing report in SSRS 2005, but I can get its ~.rdl file, no solution. Can I use ~.rdl file to repair the report?

Thank you!

View 5 Replies View Related

SQL XML :: Iterate And Modify XML File

Sep 15, 2015

I've a problem with this XML file shown below.

<Prestazione>
  <DatiPdR>
    <cod_pdr>1111111</cod_pdr>
    <Lettura>
      <let_tot_prel>3522</let_tot_prel>

[Code] ...

In particular i want to replace the value of all <let_tot_prel> tags, but i can't manage to do what i want. 

View 5 Replies View Related

Get A File&#39;s Create/Modify Date

Jun 17, 1999

HOW TO GET A FILE'S CREATE DATE??
From SqlServer I need the create date of a file on C or D.
Is there a function or code to retrieve the create date,
or an API that could read and pass back the file's creation date???

View 1 Replies View Related

Cann't Remove Data File From User Database

Mar 15, 2004

We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:

Error: the file PRADATA4 cannot be removed because it is
not empty.

The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:

1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO

Can anyone help?

Thanks

View 8 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

Create/modify File With A System Stored Procedire

Nov 22, 2005

Does anybody know how to create/modify a file without using one of the system stored procedures?

Thanks

View 1 Replies View Related

Modify The RSReportServer.config File With Web Service Call

Jan 11, 2008

Is there a way to modify the RSReportServer.config file with a Web Service call. We want to install an app and have it modify the Renders. We want to remove the Tiff export for example. We also want to add simple headers to Excel. Right now we have to open the file and then make our edits.

View 1 Replies View Related

How Can We Modify The Files Path For The Database In Database Engine?

May 14, 2007

Hi, all experts here,

Thank you very much for your kind attention.

I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 5 Replies View Related

Autogrow Of File 'Prod' In Database 'Production' Was Cancelled By The User Or Timed Out

May 27, 2008

We are running Microsoft SQL 2005 Express.

All queries on our Production database are timing out. Viewing the error log file the following show up over and over again:

Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 3937 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Autogrow of file 'Prod' in database 'Production' was cancelled by user or timed out after 33156 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Our production database is about 1 gig in size with 3.5 million records. I tried setting the autogrow from 30% which it was before to 100MB, but no luck, still timing out and getting the errors above. Permission should be all good, nothing has changed.

There is about 50gigs of available disk space as well, so that's not the problem. Thanks for the help.

View 51 Replies View Related

How To Query A Dbo.file Of User-defined Database That Has The Nvarch Type In A Column?

Nov 3, 2007

Hi all,
In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table:
dbo.LabTests Column_name Type Length Prec Scale
AnalyteID int 4 10 0 (Primary Key)
AnalyteName nvarch 510
CasNumber nvarch 510
Result numeric 5 8 2
Unit nvarch 510
SampleID int 4 10 0 (Foreign Key)

AnalyteID AnalyteName CasNumber Result Unit SampleID
1 Acetone 123-456-9 134.0 ug/L 1
2 Bezene 666-12-8 2.0 ug/L 1
3 Cobalt 421-008-7 10.0 ug/L 1
4 Acetone 123-456-9 201.0 ug/Kg 2
5 Bezene 666-12-8 1.0 ug/Kg 2
6 Cobalt 421-008-7 22.0 ug/Kg 2
7 Acetone 123-456-9 357.0 ug/L 3
8 Bezene 666-12-8 9.0 ug/L 3
9 Cobalt 421-008-7 56.0 ug/L 3


When I ran the following T-SQL code:
USE ChemAveRpd

GO

SELECT *

FROM dbo.LabTests as LabResults

Where AnalyteName = Acetone

GO


I got the following error:

Msg 207, Level 16, State 1, Line 3

Invalid column name 'Acetone'.

Please help and tell me what right syntax I should write for [Where AnalyteName = Acetone] to generate a listing of LabResults for Acetone.

Thanks in advance,
Scott Chang



View 4 Replies View Related

After Replicating A Database, SP's In The Same Database Are Not Able To Modify By Other Users.

May 21, 2008

hi there,

After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...

Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

any suggessions are most welcome.

by
CHINNASAMY

View 1 Replies View Related

Modify The File Dir In A DTS Com File

Sep 14, 2000

Hi,

i made the stored proc :

ALTER PROCEDURE ExecuteDTSPackage @ServerName VarChar(15),@UserID VarChar(15),@Password varChar(15),@PackageName VarChar(50)
AS
Declare @obj int
Declare @conn int
Declare @hrobj int , @hrconn int
Declare @return varChar(255)
Declare @CMD varChar(255)
Declare @prop int
Exec @hrobj=sp_OACreate 'DTS.Package', @obj OUT
if @hrobj=0
begin
set @CMD='LoadFromStorageFile("D:logmetrixDTS_Packages DTS_Domainlev1.DTS",Null)'
EXEC @hrobj = sp_OAMethod @obj, @CMD
if @hrobj=0
begin
--SELECT FILEPROPERTY(@obj, 'Connections(0)')
--Exec @hrconn=sp_OACreate 'DTS.Package.Connections(0)', @obj OUT
--EXEC @hrconn = sp_OASetProperty @obj, 'Connections(0).Datasource', 'd:logmetrixcountrydomainlev1.csv'
EXEC @prop = sp_OAGetProperty @obj, 'CreationDate'
EXEC @prop = sp_OAGetProperty @obj, 'AutoCommitTransaction'
--print @hrconn
--print @dtcreate
IF @hrconn = 0
BEGIN
Print 'Ok'
RETURN
END
end
else print 'Error Loading Package.'
Exec sp_OADestroy @obj
end
else print 'Error loading OLE Package.'
go

I can acceed to the properties of my object DTS.package @obj but I don't know how to acceed to the properties of my first connection (I made a import with DTS Wizard from a flat data file to a database table (SQL Server). I need to change the filedir dynamically. It would be in the datasource or datafile property of this connection.

How can I change this fiel directory ???

Thanks to your answer

Axel

View 1 Replies View Related

Create Or Modify MSDE Database

Dec 4, 2003

Hi,
On Administrator session, I have created an CUSTOMER MSDE database. That's OK.

On user session, I want to access on my database CUSTOMER but I have on error : "Unable to connect to the database". And, in French : "Echec de la connexion de l'utilisateur Machine_Name/User_Name".

What is the error ? I don't know what to do any more.
How to give right to the user ? Have you an idea ?

Thank's.

Patrice A. BONNEFOY.

View 2 Replies View Related

What Am I Doing Wrong? I Just Want To Modify My Database From A Webform.

Feb 20, 2006

Windows XP
SQL Expresss 2005
SQL Express 2005 Manager
 
I simply want to edit and data I have in a database that I made with Express Manager. This worked before .Net 2.0. Now, I have the table displayed as a datagrid with an "edit" pushbutton associated with each row in the database table. When I click on the "edit" pushbutton to edit a specific row in the database table, instead of posting me to a page where the chosen row is editable, I am posted back to the same page without the chosen row availible to be edited.
What am I doing wrong? How can I fix this? I am totally new to this so any help would be appreciated.
Cheers!
<%@ Page Language="C#" ContentType="text/html" ResponseEncoding="iso-8859-1" EnableEventValidation="false" %><%@ Register TagPrefix="MM" Namespace="DreamweaverCtrls" Assembly="DreamweaverCtrls,version=1.0.0.0,publicKeyToken=836f606ede05d46a,culture=neutral" %><MM:DataSet id="dsParts"runat="Server"IsStoredProcedure="false"ConnectionString='<%# System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_STRING_PartsAreUs"] %>'DatabaseType='<%# System.Configuration.ConfigurationSettings.AppSettings["MM_CONNECTION_DATABASETYPE_PartsAreUs"] %>'CommandText='<%# "SELECT * FROM dbo.Parts" %>'Debug="true">  <EditOps>    <EditOpsTable Name="dbo.Parts" />      <Parameter Name="Name" Type="VarChar" />      <Parameter Name="Description" Type="NVarChar" />      <Parameter Name="Price" Type="Money" />      <Parameter Name="PartID" Type="Int" IsPrimary="true" />    </EditOps></MM:DataSet><MM:PageBind runat="server" PostBackBind="true" /><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><title>Untitled Document</title></head><body><form runat="server">  <asp:DataGrid id="dgParts"   runat="server"   AllowSorting="False"   AutoGenerateColumns="false"   CellPadding="3"   CellSpacing="0"   ShowFooter="false"   ShowHeader="true"   DataSource="<%# dsParts.DefaultView %>"   PagerStyle-Mode="NextPrev"         DataKeyField="PartID"  onCancelCommand="dsParts.OnDataGridCancel"   onEditCommand="dsParts.OnDataGridEdit"   onUpdateCommand="dsParts.OnDataGridUpdate"   onItemDataBound="dsParts.OnDataGridItemDataBound" >    <HeaderStyle HorizontalAlign="center" BackColor="#E8EBFD" ForeColor="#3D3DB6" Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Bold="true" Font-Size="smaller" />    <ItemStyle BackColor="#F2F2F2" Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Size="smaller" />    <AlternatingItemStyle BackColor="#E5E5E5" Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Size="smaller" />    <FooterStyle HorizontalAlign="center" BackColor="#E8EBFD" ForeColor="#3D3DB6" Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Bold="true" Font-Size="smaller" />    <PagerStyle BackColor="white" Font-Name="Verdana, Arial, Helvetica, sans-serif" Font-Size="smaller" />    <Columns>    <asp:EditCommandColumn         ButtonType="PushButton"         CancelText="Cancel"         EditText="Edit"         HeaderText="Edit"         UpdateText="Update"         Visible="True"/>    <asp:BoundColumn DataField="PartID"         HeaderText="PartID"         ReadOnly="true"         Visible="True"/>    <asp:BoundColumn DataField="Name"         HeaderText="Name"         ReadOnly="false"         Visible="True"/>    <asp:BoundColumn DataField="Description"         HeaderText="Description"         ReadOnly="false"         Visible="True"/>    <asp:BoundColumn DataField="Price"         HeaderText="Price"         ReadOnly="false"         Visible="True"/>    </Columns>  </asp:DataGrid></form></body></html>
 

View 3 Replies View Related

Unable To Modify Database Design

Mar 1, 2006

I have replaced the installation of MSDE 1.0 with MSDE 2000.



Prior to installation of MSDE 2000, I can perform the activities of all
database role. Now, I can only add, change and delete data - no
more database design.



What should I do to regain database design permission?



I'm using "sa" user with blank password when I login to MS Access Project.

View 5 Replies View Related

How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?

May 22, 2008



Hi,
Please give the T-SQL script for this ? Thanks

Shanth


View 4 Replies View Related

How To Change The Database Context And Modify Tables?

Oct 4, 2007

I have 30+ databases in sql 2000.
I want to dynalically loop though each of the databases and alter a column named 'EmpName' for all the 'Employees' tables in that database.
Here is my attempt.use Master
go
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)declare @TABLE_NAME NVarchar(100)
DECLARE curDB CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name FROM sysdatabases
OPEN curDB
FETCH NEXT FROM curDB INTO @dbname
While @@FETCH_STATUS=0
Begin
      SET @SQL2 = 'USE ' + @DBNAME + ' ALTER TABLE ' + @TABLE_NAME + ' MODIFY EmpName varchar(100)'
     from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%Employees%'      PRINT @SQL2
      EXECUTE SP_EXECUTESQL @SQL2
fetch next from curDB into @DBName
end
Close curDB
End
When i execute the above script in master, it says 'Query executed successfully' but when i see the all the Employees table in each of the database, the required change is not made.
Can anybody explain this or give an alternate solution? 
 
 

View 2 Replies View Related

The Database File May Be Corrupted. Run The Repair Utility To Check The Database File. [ Database Name = SDMMC Storage Cardwinp

Oct 9, 2007

yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me

View 4 Replies View Related

Integration Services :: How To Capture Changed Data Of 2 CSV Files And Modify It In Oracle Database

May 6, 2015

I need reflecting changes of csv file in oracle DB. Suppose,  I load single csv file in oracle DB which contains 10 rows. After some time, I have loaded another CSV file which has the modified row of the previously loaded csv file. So, how can I capture the CSV changes and how it is going to get reflected in oracle DB?There is no unique column in csv file to identify particular row.

View 3 Replies View Related

Sql 2005 Install Error. MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Jun 15, 2006

I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message



native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.


I'll try to post the full log in a new post.



View 11 Replies View Related

Creating Database User And Set User To Owner Of Database

Nov 30, 2007



Hi there,

I want to create database login and set that login to owner of the database? Can anyone help me?

thanks

View 3 Replies View Related

Cannot Open User Default Database. Login Failed For User 'NT AUTHORITYNETWORK SERVICE'

Mar 3, 2008

 Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'.  I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed 

View 3 Replies View Related

Second User Trying To Connect Generates: Cannot Open User Default Database. Login Failed.

Apr 25, 2007

Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"

I would be grateful if you can help me

View 1 Replies View Related

Reporting Services :: Login Failed For User (xxxx) - Cannot Open User Default Database

Jul 21, 2015

Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.

I can run the report in preview mode from bits on the sql 2008 r2 server.

I have tested the connection of the  deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.

I can logon to sql express 2012 using  management studio and logon as the user and access the database run stored procedures etc.

windows server 2003, sql server 2008 R2 reporting services server.

windows server 2012 sql express 2012 data source database location.

View 13 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.



Any help with this process?

View 1 Replies View Related

Cannot Open User Default Database. User Login Failed.

Jan 15, 2004

Hi,

I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...

We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.

The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.

I have checked for orphaned users and the user I am logging in with from the webpage is not listed.

So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.

Thanks for your help, appreciated.

James.

View 9 Replies View Related

Set Database Back To Multi User From Single User

Feb 27, 2008

I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.

While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.

I cannot now reset that database to Multi User, even by executing the query

exec sp_dboption 'database_name', 'single user', ''FALSE'

again by using the Management Express Tool.

Can someone please help, in plain english???? Thanks

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved